Search Results pay_temp_balance_adjustments




Overview
Explore in AI Chat

Overview

PAY_TEMP_BALANCE_ADJUSTMENTS is a payroll module table owned by the HR schema in Oracle E-Business Suite (validated on 12.1.1 and 12.2.2). As its description states, it holds temporary data used by the balance initialization process. During balance initialization — typically executed when implementing Oracle Payroll, migrating legacy balances, or reseeding a balance after configuration changes — the payroll engine must stage adjustment entries before they are merged into permanent balance results. This table serves as that staging area.

The temporary nature of the table is significant: rows are transient, scoped to a specific initialization or batch run, and are purged once the initialization completes successfully. The table is not a transactional system of record for payroll results; the authoritative post-initialization balances reside in the standard balance and run result tables.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is satellite-leaning. This is a suggestion rather than a documented fact: the table records descriptive attributes (adjustment amounts, dates, source references) attached to batch and balance dimension keys, which is the characteristic shape of a satellite rather than a hub or link.

Key Information Stored

The table contains 17 documented columns. The most consequential for functional and technical analysis are:

  • BATCH_LINE_ID — Foreign key to PAY_BALANCE_BATCH_LINES; ties each temporary adjustment to a specific line within a balance batch. This is the primary operational linkage during the initialization run.
  • BALANCE_DIMENSION_ID — Foreign key to PAY_BALANCE_DIMENSIONS; identifies the balance and its dimensional context (e.g., assignment, tax unit, jurisdiction) that the adjustment targets.
  • BALANCE_TYPE_ID — Identifies the balance type being initialized, distinguishing, for example, a payment balance from a deduction or accrual balance.
  • ADJUSTMENT_AMOUNT — The monetary or numeric value to be applied to the balance during initialization.
  • ADJUSTMENT_DATE and EXPIRY_DATE — Define the effective window of the adjustment; the expiry date in particular governs how long the staged value remains valid before being discarded.
  • ELEMENT_LINK_ID — Links the adjustment to a payroll element entry, anchoring the amount to a specific earning or deduction.
  • JC_INPUT_VALUE_ID and IBF_INPUT_VALUE_ID — Reference input values associated with the element link, supporting both the standard and the indirect (IBF) input frameworks.
  • TAX_UNIT_ID and JURISDICTION_CODE — Provide the statutory context required when the balance is tax-sensitive.
  • ORIGINAL_ENTRY_ID — Preserves a pointer to the originating entry, supporting traceability of where the adjustment came from.
  • SOURCE_ID, SOURCE_NUMBER, SOURCE_TEXT, and SOURCE_TEXT2 — Generic provenance fields identifying the upstream system, process, or user that generated the adjustment.
  • RUN_TYPE_ID — Identifies the payroll run type under which the initialization is executing.

The documented metadata does not identify an explicitly named single-column surrogate primary key or a declared unique business key; the de facto row identity is the combination of the batch line and balance dimension references. Analysts should confirm the physical constraints in their specific instance before assuming a primary key.

Common Use Cases and Queries

The principal practical use is diagnostic: confirming which adjustments were staged, and for which batch line, when a balance initialization run produces unexpected results. A typical query joins the table to the batch line and balance dimension parents:

  • Verifying staged adjustments for a batch: select BATCH_LINE_ID, BALANCE_DIMENSION_ID, ADJUSTMENT_AMOUNT, and ADJUSTMENT_DATE filtered by the batch line in question.
  • Auditing provenance: group by SOURCE_ID and SOURCE_TEXT to determine which upstream load or process contributed the most adjustments.
  • Detecting expiring adjustments: filter on EXPIRY_DATE to find staged rows that will be discarded if the initialization is not completed.
  • Reconciliation reporting: sum ADJUSTMENT_AMOUNT by BALANCE_TYPE_ID and compare against the permanent balance results after initialization to identify variance.

Because the data is temporary, any query should include batch scoping; unbounded queries risk returning rows from concurrent initialization runs.

Related Objects

The documented foreign keys point outward to parent tables, and the balance initialization process provides the functional context:

  • PAY_BALANCE_BATCH_LINES — Joined via BATCH_LINE_ID; the direct parent defining the batch line each adjustment belongs to.
  • PAY_BALANCE_DIMENSIONS — Joined via BALANCE_DIMENSION_ID; supplies the balance and dimension definition targeted by the adjustment.
  • PAY_BALANCE_BATCH_HEADERS — The header above the batch lines; reachable indirectly through PAY_BALANCE_BATCH_LINES and used to identify the overall initialization run.
  • PAY_BALANCE_TYPES — Referenced through BALANCE_TYPE_ID; defines the balance being initialized.
  • PAY_ELEMENT_LINKS — Referenced through ELEMENT_LINK_ID; connects the adjustment to the element entry generating the value.
  • PAY_RUN_TYPES — Referenced through RUN_TYPE_ID; identifies the payroll run type context.
  • PAY_BALANCES and the balance results tables — The permanent destinations that receive the initialized values after this staging table is processed and purged.

Because the table is a staging artifact, referential integrity is enforced only to the batch line and balance dimension parents; downstream dependencies are functional rather than declarative.