Search Results igs_pe_depd_active_pk

Overview

IGS_PE_DEPD_ACTIVE is a transactional table within the Oracle E-Business Suite Student System (IGS) product family. Its documented description — "Person Dependent Relationship Active Information" — indicates that it stores the active-state records associated with a person's dependent relationships, such as dependents tied to a student, applicant, or constituent record. The table operates as an append-oriented activity/status ledger rather than a static master record: each row captures an action performed against a dependent relationship as of a specific effective date.

Applying a heuristic Data Vault classification, the table most closely resembles a satellite structure. Its composite primary key combines a relationship identifier with an action code and an effective date, which is characteristic of a time-stamped, descriptive record attached to a parent business key (the relationship). Because the mined FK structure yields no outbound foreign keys, the table is classified as standalone, meaning it carries its own business key and does not participate in enforced referential integrity constraints within the documented schema. This classification is a modeling suggestion only, inferred from the key structure and absence of FK metadata.

Key Information Stored

The documented physical schema for ETRM 12.1.1 contains 11 columns. The most significant are:

• RELATIONSHIP_ID — The business identifier for the dependent relationship to which the row pertains. It forms the leading column of the unique index and is part of the composite primary key.
• ACTION_CODE — Encodes the action or status transition applied to the relationship record. It participates in both the primary key and the unique business-key index, enabling multiple action events per relationship.
• EFFECTIVE_DATE — The date on which the recorded action takes effect. Together with RELATIONSHIP_ID and ACTION_CODE it completes the composite primary key, producing a fully qualified time-versioned key: IGS_PE_DEPD_ACTIVE_PK.
• REASON_CODE — A coded value describing the reason associated with the action record.
• REMARKS and OTHER_REASON_REMARKS — Free-form textual detail supporting the reason and action, used when coded values require supplementary explanation.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — The standard Oracle EBS WHO columns, providing audit lineage for row creation and modification.

The surrogate-versus-business-key distinction is explicit in the metadata: IGS_PE_DEPD_ACTIVE_PK is documented both as (ACTION_CODE, EFFECTIVE_DATE, RELATIONSHIP_ID) and, via the unique index listing, as (RELATIONSHIP_ID, ACTION_CODE, EFFECTIVE_DATE). The column order differs between the two descriptions, but the same three columns constitute the business key. There is no separate single-column surrogate key documented.

Common Use Cases and Queries

The primary reporting use is retrieving the action history for a dependent relationship in date order:

• Relationship history: SELECT relationship_id, action_code, effective_date, reason_code, remarks FROM igs_pe_depd_active WHERE relationship_id = :id ORDER BY effective_date, action_code;
• Latest action per relationship: use an analytic function or a correlated subquery on MAX(effective_date) grouped by relationship_id to derive current dependent status.
• Action-frequency analysis: aggregate COUNT(*) grouped by action_code and effective_date range for operational auditing of dependency changes.
• Audit and compliance extraction: filter on creation_date or last_update_date to report records created or modified within a period, joining the WHO columns back to personnel records.

Because the table is standalone with no documented FK constraints, queries are typically driven by relationship_id values sourced from the parent relationship tables or from application-level selection rather than implicit constraint traversal.

Related Objects

The mined metadata documents no outbound foreign keys, so related-object identification is inferential rather than constraint-verified. The most significant associations are:

• Parent dependent-relationship entity — the table whose primary key supplies RELATIONSHIP_ID; IGS_PE_DEPD_ACTIVE acts as its action/status child.
• Related person or party tables — keyed by the person or dependent identifier carried within the relationship record.
• Reason and action code lookups — reference sets resolving REASON_CODE and ACTION_CODE to descriptions.
• IGS person/student base tables and views in the same schema that expose dependent relationship data to inquiry screens.
• Standard WHO audit joins to personnel and FND user tables via CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

Given the absence of documented foreign keys, integrators should confirm actual join columns against the deployed 12.2.2 schema before relying on any inferred relationship.