Results for “pay_assignment_latest_balances”

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

Overview

PAY_ASSIGNMENT_LATEST_BALANCES is a denormalised payroll table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores, at the assignment level, the most recent computed value of each defined payroll balance, together with its immediately preceding value. Rather than forcing a query to scan the full history held in the balance result tables, the payroll engine maintains this table as a fast-access snapshot of "latest" figures per assignment and defined balance, which makes it a common target for ad-hoc balance lookups, reconciliation and reporting.

Its role is tightly coupled to the payroll run: as assignment actions are processed, the resulting balance values are written or refreshed into this table. The ASSIGNMENT_ACTION_ID and PREV_ASSIGNMENT_ACTION_ID columns anchor each row to the run or action that produced the current and previous values, so the table also acts as a lightweight audit pointer back into the payroll action stream. Following the heuristic Data Vault classification supplied in the metadata, this object is satellite-leaning: it describes attributes (values) that are dependent on a business key formed by the assignment and defined balance, with foreign keys radiating outward to supporting dimensions and links.

Key Information Stored

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

• LATEST_BALANCE_ID — the surrogate primary key, defined by the unique index PAY_ASSIGNMENT_LATEST_BALA_PK. It uniquely identifies each snapshot row and is the only documented business-key candidate.
• ASSIGNMENT_ID — the assignment to which the balance value belongs; the effective business key for most queries.
• DEFINED_BALANCE_ID — foreign key to PAY_DEFINED_BALANCES, identifying which balance (for example a gross, net or accumulative balance) the row records.
• VALUE — the current latest balance value for the assignment/defined balance combination.
• PREV_BALANCE_VALUE — the previously recorded value, enabling delta and trend analysis without querying history tables.
• ASSIGNMENT_ACTION_ID — foreign key to PAY_ASSIGNMENT_ACTIONS, linking the row to the action that generated the current value.
• PREV_ASSIGNMENT_ACTION_ID — the action that produced the previous value, providing a comparison point for the current row.
• EXPIRED_ASSIGNMENT_ACTION_ID and EXPIRED_VALUE — capture balances associated with actions that have since expired or been superseded, which is material when retroactive payroll processing extends or reverses an assignment's results.
• INSTANCE_ID — a context identifier used when multiple balance instances (for example different payroll runs or consolidation contexts) must be distinguished for the same assignment.

Together, ASSIGNMENT_ID and DEFINED_BALANCE_ID form the natural (non-enforced) composite key, while LATEST_BALANCE_ID provides the enforced surrogate access path.

Common Use Cases and Queries

The primary practical use is retrieving a single, current balance for an assignment without traversing the higher-volume balance history. A typical pattern joins to the balance definition to obtain a human-readable name:

• Lookup of a specific balance: SELECT a.assignment_id, d.defined_balance_name, a.value FROM pay_assignment_latest_balances a, pay_defined_balances d WHERE a.defined_balance_id = d.defined_balance_id AND a.assignment_id = :p_assignment_id.
• Delta/trend reporting: comparing VALUE against PREV_BALANCE_VALUE to flag assignments whose balances changed sharply between actions.
• Reconciliation to the action stream: joining on ASSIGNMENT_ACTION_ID to PAY_ASSIGNMENT_ACTIONS to trace which run posted the current figure.
• Detecting stale or expired data: filtering rows where EXPIRED_ASSIGNMENT_ACTION_ID is populated to identify superseded balances.

Because the table is denormalised and maintained by payroll processing, reports should treat it as a convenience cache and reconcile against the full balance results when absolute accuracy is required.

Related Objects

• PAY_DEFINED_BALANCES — joined via DEFINED_BALANCE_ID; supplies the balance definition and name.
• PAY_ASSIGNMENT_ACTIONS — joined via ASSIGNMENT_ACTION_ID and PREV_ASSIGNMENT_ACTION_ID; identifies the run/action that generated the values.
• PAY_BALANCE_RESULTS / balance result tables — the historical source from which the latest snapshot is derived.
• PAY_ASSIGNMENT_LATEST_BALANCES primary key index (PAY_ASSIGNMENT_LATEST_BALA_PK) — the enforced unique access path on LATEST_BALANCE_ID.
• PER_ASSIGNMENTS_F — context for ASSIGNMENT_ID when assignment details are required.
• Payroll balance extraction APIs — consume these latest values for fast balance retrieval in FastFormula and interfaces.