Search Results other_reason_remarks

Overview

The IGS.IGS_PE_DEPD_ACTIVE table stores active dependency relationship information for persons within the Oracle E-Business Suite IGS (Intelligence/Student System) schema. Its documented title, "Person Dependent Relationship Active Information," indicates that it tracks the lifecycle of dependent relationships — specifically activation and termination events — such as those maintained for student or person dependents in Oracle Student System and related modules. Each row captures a state-changing action applied to a dependent relationship, along with the effective date of that action, the reason for termination where applicable, and supporting remarks.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and is owned by the IGS schema. It is registered in FND Design Data as IGS.IGS_PE_DEPD_ACTIVE and holds VALID status. The documented FK analysis classifies this object as a standalone entity, and the heuristic Data Vault classification mined from its key structure is likewise standalone. In modeling terms, this object behaves least like a pure satellite and more like a self-contained transactional/history record: it carries descriptive attributes (REASON_CODE, REMARKS, OTHER_REASON_REMARKS) that are scoped to a relationship identifier and a dated action, but no documented foreign key constrains those attributes to a parent hub. It is important to note that the table does not reference any database object per the ETRM dependency listing, though it is referenced by the APPS synonym IGS_PE_DEPD_ACTIVE.

Key Information Stored

The table contains eleven documented columns, anchored by a composite unique index and a set of standard auditing fields.

• RELATIONSHIP_ID (NUMBER(15)) — The identifier of the dependent relationship being acted upon. It is the first column of the primary key index IGS_PE_DEPD_ACTIVE_PK (RELATIONSHIP_ID, ACTION_CODE, EFFECTIVE_DATE) and is the primary join key back to relationship-defining parent objects.
• ACTION_CODE (VARCHAR2(30)) — Documents the action applied, described in the metadata as "Terminate or Activate." It is a business-key component of the unique index.
• EFFECTIVE_DATE (DATE) — The date on which the action takes effect; also a business-key component of the unique index.
• REASON_CODE (VARCHAR2 ) — The termination reason code.
• REMARKS (VARCHAR2(500)) — Free-text remarks associated with the record.
• OTHER_REASON_REMARKS (VARCHAR2(500)) — Captures the reason text when the termination or end-dependent reason is "OTHER," complementing REASON_CODE with human-readable justification.
• CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who columns recording insert and last-update audit context.

The surrogate primary key is the composite of RELATIONSHIP_ID, ACTION_CODE, and EFFECTIVE_DATE, which also serves as the unique business-key candidate. No separate single-column surrogate exists.

Common Use Cases and Queries

Typical reporting centers on reconstructing the activation/termination history of dependent relationships and auditing the reasons applied at each transition. The following query returns the full lifecycle for a single relationship, ordered chronologically:

• SELECT RELATIONSHIP_ID, ACTION_CODE, EFFECTIVE_DATE, REASON_CODE, REMARKS, OTHER_REASON_REMARKS FROM IGS.IGS_PE_DEPD_ACTIVE WHERE RELATIONSHIP_ID = :p_rel_id ORDER BY EFFECTIVE_DATE;
• Filter for terminations requiring free-text justification: SELECT RELATIONSHIP_ID, EFFECTIVE_DATE, OTHER_REASON_REMARKS FROM IGS.IGS_PE_DEPD_ACTIVE WHERE ACTION_CODE = 'TERMINATE' AND OTHER_REASON_REMARKS IS NOT NULL;
• Point-in-time active checking: retrieve the latest action per relationship using an analytic rank over EFFECTIVE_DATE.

Analysts commonly join RELATIONSHIP_ID to the parent dependent-relationship definition to enrich reports with person names, relationship types, and start/end dates.

Related Objects

Per the documented dependency listing, IGS_PE_DEPD_ACTIVE does not reference other database objects via foreign keys, and it is referenced only by the APPS synonym of the same name. Consequently, no FK-verified related tables are documented in the ETRM metadata. In practice, integration occurs through the shared RELATIONSHIP_ID column:

• Parent dependent-relationship definition table — joined on RELATIONSHIP_ID to resolve the person and dependent identifiers.
• APPS.IGS_PE_DEPD_ACTIVE — the APPS-layer synonym through which the table is accessed.
• IGS_PE_DEPD_ACTIVE_PK — the unique index enforcing the composite business key.

The absence of declared foreign keys means referential integrity for RELATIONSHIP_ID is enforced by the owning application logic rather than the database.