Results for “pqh_copy_entity_txns_uk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR.PQH_COPY_ENTITY_TXNS is the transactional header table for the Oracle EBS "Mass Copy Entity" / batch copy process delivered through the PQH (Public Sector/HR) product family. Each row represents a single batch run that copies or redistributes entity records — for example grade ladders, position hierarchies, or organization structures — across a business group. The table records the batch identifier, the effective action date, the copy strategy, and the number of copies to be produced, serving as the parent for the attributes, preferences, and results tables that capture the detail of the run.

Modeled against Data Vault conventions, the mined foreign-key structure classifies this table as satellite-leaning. It carries descriptive, run-specific attributes (dates, status, replacement strategy) keyed by a surrogate batch identifier, while most of its foreign keys point to reference/category dimension tables. A satellite classification is therefore a reasonable modeling suggestion, with the primary key acting as the parent hub reference for the dependent results and attribute tables.

Key Information Stored

The surrogate primary key is COPY_ENTITY_TXN_ID (NUMBER(15)), documented as the batch identifier and enforced by PQH_COPY_ENTITY_TXNS_PK. It uniquely identifies each copy batch and is the column referenced by all child tables.

• DISPLAY_NAME (VARCHAR2(100)) — the human-readable batch name; part of the unique business key.
• TRANSACTION_CATEGORY_ID — FK to PQH_TRANSACTION_CATEGORIES; part of the unique business key.
• CONTEXT_BUSINESS_GROUP_ID — the business group (organization unit) in which the batch operates; part of the unique business key and the focus of the user's search term.
• CONTEXT (VARCHAR2(27)) — context identifier, FK to PQH_COPY_ENTITY_CONTEXTS.
• ACTION_DATE — the effective date on which the copy transaction applies.
• SRC_EFFECTIVE_DATE — the effective date used to query the original (source) record.
• TXN_CATEGORY_ATTRIBUTE_ID — FK to PQH_TXN_CATEGORY_ATTRIBUTES, identifying a special operation attribute.
• REPLACEMENT_TYPE_CD (VARCHAR2(30)) — the special operation type (Replace, Prefix, or Suffix).
• START_WITH (VARCHAR2(255)), INCREMENT_BY, and NUMBER_OF_COPIES — control the naming and count of generated copies.
• STATUS — lifecycle state of the batch.
• WF_TRANSACTION_CATEGORY_ID — FK to PQH_TRANSACTION_CATEGORIES used for workflow-driven processing.
• DATETRACK_MODE — controls date-tracking behavior of the copy.

The unique index PQH_COPY_ENTITY_TXNS_UK (TRANSACTION_CATEGORY_ID, CONTEXT_BUSINESS_GROUP_ID, DISPLAY_NAME) constitutes the documented business key, distinguishing it from the surrogate primary key.

Common Use Cases and Queries

Because CONTEXT_BUSINESS_GROUP_ID is the primary filter for multi-tenant isolation, it appears in nearly every query against this table. Typical reporting needs include listing all batches run for a business group, auditing copy results, and verifying batch naming conventions.

• List recent batches for a business group:
  SELECT COPY_ENTITY_TXN_ID, DISPLAY_NAME, ACTION_DATE, STATUS FROM HR.PQH_COPY_ENTITY_TXNS WHERE CONTEXT_BUSINESS_GROUP_ID = :p_bg_id ORDER BY ACTION_DATE DESC;
• Resolve the business group name via the FK to HR_ALL_ORGANIZATION_UNITS:
  SELECT t.DISPLAY_NAME, o.name FROM HR.PQH_COPY_ENTITY_TXNS t, HR.HR_ALL_ORGANIZATION_UNITS o WHERE t.CONTEXT_BUSINESS_GROUP_ID = o.organization_id;
• Join to the results table to inspect generated copies by batch.
• Validate the unique business key when troubleshooting duplicate-batch errors.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — referenced by CONTEXT_BUSINESS_GROUP_ID, resolving the business group name.
• PQH_TRANSACTION_CATEGORIES — referenced by TRANSACTION_CATEGORY_ID and WF_TRANSACTION_CATEGORY_ID.
• PQH_TXN_CATEGORY_ATTRIBUTES — referenced by TXN_CATEGORY_ATTRIBUTE_ID.
• PQH_COPY_ENTITY_CONTEXTS — referenced by CONTEXT.
• PQH_COPY_ENTITY_ATTRIBS — child table via COPY_ENTITY_TXN_ID.
• PQH_COPY_ENTITY_PREFS — child table via COPY_ENTITY_TXN_ID.
• PQH_COPY_ENTITY_RESULTS — child table via COPY_ENTITY_TXN_ID.
• BEN_COPY_ENTITY_RESULTS — beneficiary-side child table via COPY_ENTITY_TXN_ID.