Results for “ben_copy_entity_txns_vw”

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

Overview

BEN_COPY_ENTITY_TXNS_VW is a view owned by the APPS schema within the Oracle Advanced Benefits (BEN) product module. Its documented role is that of a Plan Design Transaction View. In practice, the view exposes the transaction header records that drive the "Copy Entity" plan design facility in Advanced Benefits, joining each transaction to its associated attribute definitions. This makes it a reporting and integration surface for copy transactions that create or extend benefit plan design objects, such as plans, options, and programs. Because it unifies header and attribute information in a single row-per-attribute projection, the view supports both operational diagnostics of copy transactions and downstream extracts that must carry the descriptive context of each copied entity.

Underlying Base Objects

The documented view definition selects from two synonyms that resolve to base tables in the PQH schema:

• PQH_COPY_ENTITY_TXNS — aliased CET, supplying the transaction header.
• PQH_COPY_ENTITY_ATTRIBS — aliased CEA, supplying the attribute rows attached to each transaction.

The two are joined on CET.COPY_ENTITY_TXN_ID = CEA.COPY_ENTITY_TXN_ID, producing one output row per copy entity attribute. The header supplies transaction identifiers, action and effective dates, transaction category, replacement type, incremental copy parameters, status, datetrack mode, and audit columns; the attribute side contributes row typing, information categories, up to thirty generic INFORMATION1–INFORMATION30 attribute slots, and its own object version number. The view is documented as VALID in ETRM metadata for both 12.1.1 and 12.2.2, and its structure is a straightforward inner join with no aggregation or filtering predicates.

Key Columns

• COPY_ENTITY_TXN_ID — Primary transaction identifier linking header and attribute records.
• CONTEXT_BUSINESS_GROUP_ID — The business group context under which the transaction executes; the column most commonly used to scope queries to a single business group.
• ACTION_DATE and SRC_EFFECTIVE_DATE — Dates governing when the copy action applies and the source effective date used for copying.
• TRANSACTION_CATEGORY_ID and WF_TRANSACTION_CATEGORY_ID — Category identifiers for the copy transaction, tying the record to workflow-driven processes.
• REPLACEMENT_TYPE_CD, START_WITH, INCREMENT_BY, NUMBER_OF_COPIES — Parameters controlling how names and multiple copies are generated.
• STATUS — Lifecycle state of the copy transaction.
• DATETRACK_MODE — Datetrack processing mode applied to the copied entity.
• COPY_ENTITY_ATTRIB_ID, ROW_TYPE_CD, INFORMATION_CATEGORY, INFORMATION1–INFORMATION30 — Attribute-level identity and the descriptive values that characterize the copied entity.
• OBJECT_VERSION_NUMBER columns and standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — Concurrency control and row-level audit trail.

Common Use Cases and Queries

Typical scenarios include auditing which copy transactions have run for a given business group, reviewing the attribute detail attached to each transaction, and extracting plan design copy activity for reconciliation or migration.

• List recent copy transactions for a business group:

  SELECT copy_entity_txn_id, action_date, status, context_business_group_id
  FROM   apps.ben_copy_entity_txns_vw
  WHERE  context_business_group_id = :p_business_group_id
  ORDER  BY action_date DESC;

• Inspect attribute detail for a single transaction:

  SELECT copy_entity_txn_id, copy_entity_attrib_id, row_type_cd,
         information_category, information1, information2
  FROM   apps.ben_copy_entity_txns_vw
  WHERE  copy_entity_txn_id = :p_txn_id;

• Summarize copy counts by transaction category:

  SELECT transaction_category_id, COUNT(DISTINCT copy_entity_txn_id) txn_count
  FROM   apps.ben_copy_entity_txns_vw
  WHERE  context_business_group_id = :p_business_group_id
  GROUP  BY transaction_category_id;

Because the view joins header to attribute rows, result, cardinality should be understood as attribute-level; queries seeking distinct transactions should apply DISTINCT or aggregate on COPY_ENTITY_TXN_ID.