Search Results mpf_ri




Overview
Explore in AI Chat

Overview

APPS.PAY_HK_RELEVANT_INCOME_V is a reporting view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that exposes Hong Kong Mandatory Provident Fund (MPF) Relevant Income (RI) balances at assignment and payroll period level. The view filters the underlying balances-by-date data to the specific defined balance identified by the balance name MPF_RI and the database item suffix _ASG_RI_PTD, which denotes the period-to-date Relevant Income balance for an assignment. Its role is to provide a stable, query-friendly projection of MPF RI figures aligned to payroll time periods, enabling statutory reporting, reconciliation, and downstream integration without requiring consumers to navigate the more complex, multi-purpose balances engine tables directly.

Underlying Base Objects

The documented base objects referenced by this view are:

  • PAY_HK_BALANCES_BY_DATE_V2 (VIEW) — the primary driving source. It supplies payroll_id, assignment_id, value, expiry_date, action_sequence, database_item_suffix, balance_name, and defined_balance_id. This is the Hong Kong balances-by-date view that materialises balance results.
  • PER_TIME_PERIODS (SYNONYM) — supplies payroll period boundaries (start_date, end_date) used to anchor each balance value to a payroll period.
  • HR_GENERAL (PACKAGE), HR_SECURITY (PACKAGE), and PAY_HK_EXC (PACKAGE) — supporting PL/SQL packages referenced by the definition, handling general HR lookups, security/access control, and Hong Kong-specific exception logic.

The view joins PAY_HK_BALANCES_BY_DATE_V2 to PER_TIME_PERIODS on payroll_id, and constrains the balance's expiry_date to fall between the period's start_date and end_date. A correlated subquery restricts rows to the maximum action_sequence for each assignment and defined balance at the period end date, ensuring only the latest balance result per assignment per period is returned.

Key Columns

  • PAYROLL_ID — identifies the payroll to which the balance and period belong.
  • ASSIGNMENT_ID — the employee assignment for which the Relevant Income is calculated.
  • VALUE — the period-to-date MPF Relevant Income amount for the assignment.
  • EXPIRY_DATE — the effective date of the balance result, constrained within the payroll period.
  • PERIOD_START_DATE — the start date of the payroll time period (from PER_TIME_PERIODS).
  • PERIOD_END_DATE — the end date of the payroll time period, and the anchor used for latest-action-sequence selection.

Common Use Cases and Queries

Typical uses include MPF contribution basis reporting, auditing Relevant Income per period, and feeding payroll interfaces. A representative query retrieves RI values for a payroll across a period range:

  • SELECT assignment_id, period_start_date, period_end_date, value FROM apps.pay_hk_relevant_income_v WHERE payroll_id = :p_payroll_id ORDER BY assignment_id, period_end_date;
  • Filtering a single assignment: ... WHERE assignment_id = :p_assignment_id ORDER BY period_end_date; to trace an individual's RI history.
  • Aggregating period totals: SELECT period_end_date, SUM(value) FROM apps.pay_hk_relevant_income_v GROUP BY period_end_date;

Because the view already applies the MPF_RI / _ASG_RI_PTD predicate and the latest-action-sequence filter, consumers obtain a clean, deduplicated result set suitable for statutory and reconciliation reporting in both 12.1.1 and 12.2.2.