Search Results hr_dm_phases




Overview
Explore in AI Chat

Overview

HR_DM_PHASES is a transactional control table in the Oracle E-Business Suite Human Resources (PER) product family. It records the discrete phases that make up an individual data migration run executed through the Oracle HR Data Migration (DM) framework. Each row represents one phase — for example a load, validation, or reconciliation step — belonging to a parent migration, and carries the status and timing information needed to track progress through the overall migration lifecycle. The table is owned by the HR schema and is documented as VALID in both EBS 12.1.1 and 12.2.2 environments.

From a modeling perspective, the FK structure mined from the ETRM metadata classifies HR_DM_PHASES as hub-leaning. That classification is a heuristic suggestion rather than a delivered Data Vault design; it reflects the fact that the table owns a single-column surrogate primary key (PHASE_ID) and has relatively few incoming foreign keys compared with a pure link or satellite structure. Architects building downstream warehouse models around the HR DM framework may therefore treat PHASE_ID as a natural hub key when integrating migration telemetry with other subject areas.

Key Information Stored

The table is documented with twelve columns in the 12.2.2 physical schema. The most significant are:

  • PHASE_ID — the surrogate primary key, enforced by the unique index HR_DM_PHASES_PK. This is the value propagated into dependent tables.
  • MIGRATION_ID — foreign key to HR_DM_MIGRATIONS, identifying the parent migration run to which the phase belongs. Together with PHASE_NAME this constitutes the main business-meaningful attribute combination.
  • PHASE_NAME — the descriptive label of the migration phase.
  • STATUS — the current processing state of the phase, used to determine whether a phase is pending, running, completed, or failed.
  • START_TIME and END_TIME — timestamps bracketing phase execution, enabling duration and throughput analysis.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant security group isolation in a shared application instance.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and LAST_UPDATE_DATE — the mandatory Oracle EBS WHO columns recording the creating and last-updating user, login session, and audit timestamps.

The only documented unique index is HR_DM_PHASES_PK on PHASE_ID, so PHASE_ID is the sole business-key candidate identified in the metadata; other columns are descriptive or referential.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting in-flight or historical HR data migrations. A typical query joins phases to their parent migration to report status and elapsed time:

  • SELECT p.PHASE_ID, p.PHASE_NAME, p.STATUS, p.START_TIME, p.END_TIME FROM hr.hr_dm_phases p JOIN hr.hr_dm_migrations m ON p.MIGRATION_ID = m.MIGRATION_ID WHERE m.MIGRATION_ID = :migration_id ORDER BY p.START_TIME.
  • Drill-down from a failed phase into its individual work units: SELECT i.* FROM hr.hr_dm_phase_items i WHERE i.PHASE_ID = :phase_id.
  • Reverse lookup to find which migration request triggered a given phase: SELECT r.* FROM hr.hr_dm_migration_requests r WHERE r.PHASE_ID = :phase_id.
  • Duration and SLA reporting: group by PHASE_NAME and compute the difference between END_TIME and START_TIME to profile phase performance across successive migrations.

Because the table retains the WHO audit columns, it also supports lineage questions about who initiated or last amended a phase record.

Related Objects

The metadata documents the following relationships, all keyed on PHASE_ID or MIGRATION_ID:

  • HR_DM_MIGRATIONS — referenced by HR_DM_PHASES.MIGRATION_ID; the parent migration header.
  • HR_DM_MIGRATION_REQUESTS — references HR_DM_PHASES.PHASE_ID; request-level records associated with a phase.
  • HR_DM_PHASE_ITEMS — references HR_DM_PHASES.PHASE_ID; the child detail rows describing individual items processed within a phase.
  • FND_SECURITY_GROUPS — referenced by HR_DM_PHASES.SECURITY_GROUP_ID for application-level security partitioning.

These four objects form the complete documented dependency set; analysts investigating the HR DM framework should treat HR_DM_MIGRATIONS as the header, HR_DM_PHASES as the phase-level hub, and HR_DM_PHASE_ITEMS as the leaf-level detail.