Search Results hr_dm_table_groupings_pk

Overview

HR.HR_DM_TABLE_GROUPINGS is a reference table within the Oracle E-Business Suite Human Resources schema that governs the grouping of tables processed by the Data Management (DM) framework. Its documented purpose is to organize tables into groups so that each group can be processed independently, enabling the parallel processing features of Oracle Data Pump. The metadata explicitly states that one group corresponds directly to one Data Pump batch, making this table the control point that binds a set of table definitions to a batch unit of work. The object holds a VALID status in the ETRM registry and is associated with FND design data under the namespace PER.HR_DM_TABLE_GROUPINGS, and is stored in the APPS_TS_INTERFACE tablespace, consistent with its role as a staging and setup object rather than a transactional entity.

Based on the heuristic Data Vault classification derived from the foreign key structure, this object is best modeled as a link table. It carries no descriptive attributes of its own beyond audition columns and a currently unused LINK_VALUE; instead, it resolves a many-to-many style association between groups and tables. In Data Vault terms it functions as an associative entity joining HR_DM_GROUPS and HR_DM_TABLES.

Key Information Stored

The table contains ten documented columns. The central surrogate key is GROUPING_ID, a NOT NULL NUMBER(15) that is the sole column of the unique primary key index HR_DM_TABLE_GROUPINGS_PK. This is the identifier most commonly referenced by dependent objects; users searching for hr_dm_table_groupings_pk are typically locating this constraint or the JOIN predicate built upon it.

• GROUPING_ID — System-generated surrogate primary key, unique per grouping record and the target of the PK index.
• GROUP_ID — Foreign key to HR_DM_GROUPS.GROUP_ID, identifying the owning processing group and its associated Data Pump batch. Indexed by HR_DM_TABLE_GROUPINGS_FK1 (non-unique).
• TABLE_ID — Foreign key to HR_DM_TABLES.TABLE_ID, identifying the table definition assigned to the group. Indexed by HR_DM_TABLE_GROUPINGS_FK2 (non-unique).
• LINK_VALUE — A NUMBER(15) reserved for Data Pump use; the metadata notes it is not currently used in practice.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-tenant and security-group partitioning of the setup data.
• LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard Who audit columns capturing the last modification context.
• CREATED_BY, CREATION_DATE — Standard Who columns recording record creation.

No separate business-key unique index exists beyond the surrogate primary key; uniqueness of the group-to-table pairing is therefore implicit rather than enforced by a dedicated alternate key.

Common Use Cases and Queries

Principal use cases include auditing Data Pump batch composition, troubleshooting parallel processing failures, and generating reports of which tables belong to each processing group. A typical diagnostic query joins the grouping records to both parent tables:

• SELECT g.GROUPING_ID, gr.GROUP_ID, t.TABLE_ID, g.SECURITY_GROUP_ID FROM HR.HR_DM_TABLE_GROUPINGS g, HR.HR_DM_GROUPS gr, HR.HR_DM_TABLES t WHERE g.GROUP_ID = gr.GROUP_ID AND g.TABLE_ID = t.TABLE_ID;
• Locating orphaned or mismatched grouping rows by left-joining HR_DM_GROUPS and HR_DM_TABLES and filtering on NULL foreign keys.
• Reconciling counts of tables per group to validate that each group maps cleanly to a single Data Pump batch.
• Filtering by SECURITY_GROUP_ID when reviewing configuration across operating units or ledgers.

Because the table resides in APPS_TS_INTERFACE, bulk queries should be scheduled during low-concurrency windows to avoid contention with Data Pump operations that read the same segments.

Related Objects

The object participates in a defined dependency network. It references two principal parents and one security parent, and is itself referenced by numerous materialized views that carry a GROUPING_ID column, chiefly in the DDR (Demand Driven Reporting) and MTH (manufacturing) families.

• HR.HR_DM_GROUPS — joined via HR_DM_TABLE_GROUPINGS.GROUP_ID; defines the processing group.
• HR.HR_DM_TABLES — joined via HR_DM_TABLE_GROUPINGS.TABLE_ID; defines the individual table.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; enforces data segregation.
• DDR materialized views — including DDR_SV_ORDR_MV, DDR_SV_INV_MV, DDR_SV_SHIP_MV, DDR_SV_FRCST_MV, DDR_SV_SLSR_MV, DDR_SV_EXCPTN_MV, and their _C companion views, each carrying GROUPING_ID as a dependent column.
• MTH materialized views — including MTH_RES_TXN_LT_MV, MTH_MTL_CONS_LT_MV, MTH_MTL_PROD_LT_MV, MTH_RES_REQ_LT_MV, and the MTH_EQUIP_* summary views, all referencing GROUPING_ID.

These dependencies indicate that GROUPING_ID is not merely an internal identifier but a propagated grouping tag embedded in downstream reporting structures, so changes to grouping configuration should be assessed for impact on the dependent materialized views before deployment.