Search Results pay_hk_balances_by_date_v2




Overview
Explore in AI Chat

Overview

The PAY_HK_BALANCES_BY_DATE_V2 view is an Oracle E-Business Suite database object owned by the APPS schema and defined within the PAY (Payroll) product family. As its name and documentation indicate, it is a Hong Kong localisation view, meaning it exists specifically to support statutory and legislative payroll processing requirements for Hong Kong-based organisations running Oracle Payroll. The view is registered as VALID and is intended to expose calculated payroll balance values keyed to a specific payroll action effective date, rather than the running or period-end balances that standard payroll balance views typically return.

Functionally, the view resolves payroll balance definitions against assignment actions and payroll actions, and then invokes Hong Kong specific calculation logic through the PAY_HK_EXC package to derive a computed value and an expiry date for each balance row. This makes it a specialised reporting and integration artefact: it is not a general-purpose balances view but a mechanism through which Hong Kong legislative balance rules — including effective-dated and expiry-aware balances — are surfaced to downstream reports, extracts, and interface programs.

Underlying Base Objects

The view is defined over a substantial join across payroll and HR core objects. The documented referenced objects are: FF_CONTEXTS, FF_ROUTE_CONTEXT_USAGES, PAY_ACTION_CONTEXTS, PAY_ASSIGNMENT_ACTIONS, PAY_BALANCE_DIMENSIONS, PAY_BALANCE_FEEDS_F, PAY_BALANCE_TYPES, PAY_BALANCE_TYPES_TL, PAY_DEFINED_BALANCES, PAY_PAYROLL_ACTIONS, PAY_RUN_RESULTS, PAY_RUN_RESULT_VALUES, and PER_ASSIGNMENTS_F. It also references the packages PAY_HK_EXC, HR_GENERAL, and HR_SECURITY.

The core structure links PER_ASSIGNMENTS_F (the assignment) to PAY_ASSIGNMENT_ACTIONS and PAY_PAYROLL_ACTIONS by effective date, ensuring the correct assignment context exists at the payroll action date. Balance definitions are resolved through PAY_DEFINED_BALANCES, PAY_BALANCE_DIMENSIONS, PAY_BALANCE_TYPES, and the translated PAY_BALANCE_TYPES_TL. The EXISTS and NOT EXISTS subqueries restrict the result set to balances with supported context usage (only ASSIGNMENT_ACTION_ID and TAX_UNIT_ID) and confirm that a balance feed actually drove a run result.

Key Columns

Common Use Cases and Queries

Typical usage involves Hong Kong statutory reporting and payroll reconciliation, where the calculated value and expiry date matter. A representative query filters by assignment and effective date range:

SELECT assignment_id, defined_balance_id, balance_name, value, effective_date, expiry_date FROM apps.pay_hk_balances_by_date_v2 WHERE assignment_id = :p_assignment_id AND effective_date BETWEEN :p_start AND :p_end;

The view is also frequently joined to assignment or payroll action data to build statutory extracts, and may be queried during parallel payroll runs or post-run validation to verify that Hong Kong balance figures reconcile with run results. Because it relies on row-level security packages, query performance and returned rows are influenced by the security profile of the connected user.