Search Results hr_dm_exp_imps_pk




Overview
Explore in AI Chat

Overview

The HR_DM_EXP_IMPS table is a Human Resources (PER) module object owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. According to the ETRM documentation, it holds data drawn from various source database tables that are moved to a destination database using the transportable tablespace mechanism, then uploaded in the destination database. In practice, this table functions as a staging and transport container for data migration routines, particularly within the context of HR data movement between environments or during implementation and upgrade activities.

The object carries a status of VALID and is documented with 108 columns in the 12.2.2 physical schema. Under a heuristic Data Vault classification mined from its foreign key structure, the table is treated as standalone, with no parent or child relationships to other HR business entities within the modeled vault. This classification should be regarded as a modeling suggestion rather than a definitive architectural designation, since the table is essentially generic and column-agnostic.

Key Information Stored

The table follows a highly generic, load-oriented structure. The most significant columns are:

  • EXP_IMP_ID — the surrogate primary key, enforced by the unique index HR_DM_EXP_IMPS_PK. It uniquely identifies each row in the export/import set and is the single documented business-key candidate.
  • TABLE_NAME — identifies which source table the row originated from, allowing a single physical table to hold records for multiple source objects.
  • PVAL001 through PVAL100 — a generic set of 100 positional value columns that receive source column values in a fixed order. These columns abstract the varying shapes of source tables into a uniform storage layout.
  • SECURITY_GROUP_ID — a foreign key referencing FND_SECURITY_GROUPS, providing row-level security and multi-org/operating unit partitioning consistent with EBS conventions.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS who-columns that record the audit trail of row creation and last modification.

The surrogate key EXP_IMP_ID should not be confused with any business identifier from the source system; the actual business keys are carried positionally within the PVAL columns and must be interpreted using TABLE_NAME and the source table definition.

Common Use Cases and Queries

This table is primarily encountered during data migration and transportable tablespace operations. Typical scenarios include verifying that a migration completed successfully, auditing which source tables were included in a run, and reconciling transferred row counts before and after upload. A basic inventory query might group rows by source table:

  • SELECT TABLE_NAME, COUNT(*) FROM HR.HR_DM_EXP_IMPS GROUP BY TABLE_NAME;
  • SELECT EXP_IMP_ID, TABLE_NAME, PVAL001, PVAL002 FROM HR.HR_DM_EXP_IMPS WHERE TABLE_NAME = :source_table;
  • SELECT * FROM HR.HR_DM_EXP_IMPS WHERE SECURITY_GROUP_ID = :sg_id;

Because the value columns are generic, reporting must map positional columns back to source column names using the originating table’s definition. The who-columns support auditing of when a migration batch was staged.

Related Objects

Relationship data shows a single documented foreign key. The most significant related objects are:

  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID, establishing security-group ownership.
  • HR_DM_EXP_IMPS_PK — the primary key unique index on EXP_IMP_ID.
  • Source HR tables named in TABLE_NAME — the origin objects whose rows are staged here.
  • PER module transport/upload routines — the processes that populate and consume this staging table.

Given its standalone classification, downstream dependencies are limited, and the table functions chiefly as a migration conduit rather than a transactional HR entity.