Search Results per_person_list_changes_pk




Overview
Explore in AI Chat

Overview

HR.PER_PERSON_LIST_CHANGES is a transactional staging table within the Oracle E-Business Suite Human Resources (HR) schema. It records ex-employees who are appended to the PER_PERSON_LIST population by the LISTGEN concurrent program. Rows are inserted automatically whenever an employee is terminated; exactly one row is created for each security profile under which the employee was previously visible. This behavior allows the person list generation process to reconcile terminated workers against the security profiles that historically exposed them, supporting cleanup and refresh of person list membership.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its heuristic Data Vault classification, mined from the foreign key structure, is satellite-leaning. As a modeling suggestion, this reflects the table's role as a child of PER_SECURITY_PROFILES: it stores descriptive state (inclusion and termination flags) keyed to a parent entity rather than acting as an independent hub or an associative link.

Key Information Stored

The physical schema documents eight columns. The most significant are the following:

  • PERSON_ID (NUMBER(10), mandatory): Foreign key to PER_PEOPLE identifying the terminated employee.
  • SECURITY_PROFILE_ID (NUMBER(15), mandatory): Foreign key to PER_SECURITY_PROFILES identifying the security profile in which the person was previously visible.
  • INCLUDE_FLAG (VARCHAR2(30)): Indicates whether the person should be included in the person list (Y/N).
  • TERMINATION_FLAG (VARCHAR2(30)): Set to Y when the person appears in the list because they are terminated.
  • REQUEST_ID (NUMBER(15)): Standard Who column capturing the concurrent request that created the row.
  • PROGRAM_APPLICATION_ID (NUMBER(15)): Standard Who column identifying the application of the concurrent program.
  • PROGRAM_ID (NUMBER(15)): Standard Who column identifying the concurrent program.
  • PROGRAM_UPDATE_DATE (DATE): Standard Who column recording the program update timestamp.

The surrogate primary key is PER_PERSON_LIST_CHANGES_PK, a unique index over (PERSON_ID, SECURITY_PROFILE_ID) in APPS_TS_TX_IDX. Because this composite is enforced as unique, it also serves as the business-key candidate. A secondary non-unique index, PER_PERSON_LIST_CHANGES_FK2, exists on SECURITY_PROFILE_ID to support the foreign key to PER_SECURITY_PROFILES.

Common Use Cases and Queries

The primary use case is supporting the LISTGEN process and auditing which security profiles were affected when employees terminated. Administrators and developers query this table to diagnose person list generation results, confirm that terminated employees were removed from the correct profiles, and reconcile security profile membership.

A representative query to list all terminated persons captured for a given security profile:

  • SELECT PERSON_ID, SECURITY_PROFILE_ID, INCLUDE_FLAG, TERMINATION_FLAG FROM HR.PER_PERSON_LIST_CHANGES WHERE SECURITY_PROFILE_ID = :profile_id AND TERMINATION_FLAG = 'Y';

Joining to PER_PEOPLE and PER_SECURITY_PROFILES produces a human-readable report of affected employees by profile. Filtering on REQUEST_ID or PROGRAM_UPDATE_DATE isolates the output of a specific LISTGEN run, which is useful for troubleshooting concurrent request failures.

Related Objects

The most significant objects related to this table, based on the documented foreign key and dependency data, include:

  • HR.PER_SECURITY_PROFILES — parent table referenced by SECURITY_PROFILE_ID via PER_PERSON_LIST_CHANGES_FK2.
  • HR.PER_PEOPLE — referenced by PERSON_ID (per column comments) as the source of employee identity.
  • HR.PER_PERSON_LIST — the target person list populated by LISTGEN, to which terminated rows are appended.
  • APPS.PER_PERSON_LIST_CHANGES — the APPS synonym exposing the HR table to application code.
  • PUBLIC.PER_PERSON_LIST_CHANGES — the public synonym for cross-schema access.

These dependencies confirm the table's position as a satellite entity within the person list and security profile domain, driven exclusively by the LISTGEN concurrent program.