Search Results pay_batch_control_totals_fk1

Overview

HR.PAY_BATCH_CONTROL_TOTALS is a transactional table in the Oracle E-Business Suite Payroll (PAY) product, owned by the HR schema and registered in FND Design Data as PAY.PAY_BATCH_CONTROL_TOTALS. It stores user-defined control total definitions that are used to validate batch data during the Batch Element Entry (BEE) process, commonly referred to as PAYLINK. Control totals provide a reconciliation checkpoint: a user declares an expected value for a batch, and the system compares that declared value against the calculated value produced by the batch to confirm the data loaded correctly and completely.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 20, and its unique index is placed in APPS_TS_TX_IDX. The ETRM metadata classifies this object heuristically as satellite-leaning. In Data Vault modeling terms, this is best understood as a descriptive satellite attached to the batch header: it carries the attributes that describe the control total checks applied to a single batch (status, expected value, and control type), rather than introducing a new business entity or resolving a many-to-many relationship. The parent hub concept is carried by PAY_BATCH_HEADERS.

Key Information Stored

The table contains six documented columns. The most significant are:

• BATCH_CONTROL_ID (NUMBER(15)) — Surrogate primary key of the table, enforced by the unique index PAY_BATCH_CONTROL_TOTALS_PK. This is the only documented business-key candidate and is system-managed rather than user-entered.
• BATCH_ID (NUMBER(15)) — Foreign key to PAY_BATCH_HEADERS, identifying the batch against which the control total is validated. Indexed by the non-unique index PAY_BATCH_CONTROL_TOTALS_FK1.
• CONTROL_TYPE (VARCHAR2(30)) — A user-defined lookup value identifying the type of control total being applied (for example, a particular element, hours, or monetary category).
• CONTROL_TOTAL (VARCHAR2(30)) — The expected value entered by the user, stored as text, against which the calculated value is checked.
• CONTROL_STATUS (VARCHAR2(30)) — The status of the control check, indicating whether the declared total has been satisfied relative to the batch calculation.
• OBJECT_VERSION_NUMBER (NUMBER) — System-generated row version that increments by one on each update, supporting optimistic locking in the OAF framework.

Because CONTROL_TOTAL and CONTROL_TYPE are free-format VARCHAR2 columns, the valid values are governed by configuration rather than by a documented foreign key to a lookup table.

Common Use Cases and Queries

The primary use case is auditing and reconciling Batch Element Entry loads. Payroll administrators and support analysts query the table to verify that expected totals match calculated results before a batch is committed for processing, and to diagnose batches that failed a control check.

• List all control totals for a given batch: SELECT BATCH_CONTROL_ID, CONTROL_TYPE, CONTROL_TOTAL, CONTROL_STATUS FROM HR.PAY_BATCH_CONTROL_TOTALS WHERE BATCH_ID = :batch_id;
• Find batches with unmet control checks: filter on CONTROL_STATUS to surface outstanding reconciliation items for a period.
• Join to the batch header on PAY_BATCH_CONTROL_TOTALS.BATCH_ID = PAY_BATCH_HEADERS.BATCH_ID to report control totals alongside batch name, effective date, and action parameters.
• Version-aware updates: include OBJECT_VERSION_NUMBER in the WHERE clause of any DML to ensure the row has not changed since it was read.

Related Objects

The most significant related objects derived from the documented relationships are:

• HR.PAY_BATCH_HEADERS — Referenced by the foreign key on BATCH_ID; the batch to which each control total belongs.
• PAY_BATCH_CONTROL_TOTALS_PK — Unique index on BATCH_CONTROL_ID.
• PAY_BATCH_CONTROL_TOTALS_FK1 — Non-unique index on BATCH_ID supporting the header join.
• PAY_BATCH_CONTROL_TOTALS_BRUID and PAY_BATCH_CONTROL_TOTALS_OVN — Supporting database triggers that populate the primary key and maintain the object version number respectively.
• APPS.PAY_BATCH_CONTROL_TOTALS — The APPS-layer synonym through which the table is typically accessed in application SQL.
• Batch Element Entry (PAYLINK) — The concurrent/online process that consumes these control total definitions to validate batch data.