Search Results assessed_ri




Overview
Explore in AI Chat

Overview

PAY_HK_ASG_MPF_DATA_V is a Hong Kong localisation view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) module. It is documented as VALID in both ETRM 12.1.1 and 12.2.2. The view exposes Mandatory Provident Fund (MPF) processing data at the level of the payroll run result, correlating each MPF-related run result with its assignment action, the MPF contribution period, and the assessed relevant income figure returned by a server-side function. Its principal role is to provide a denormalised, query-ready projection of data that would otherwise require multiple joins across run results, run result values, and input value definitions. This makes it suitable for payroll reporting, statutory MPF submission extracts, payslip archive generation, and downstream integration where a compact MPF record set is required. The inclusion of the ASSESSED_RI column, computed through the PAY_HK_PAYSLIP_ARCHIVE.GET_ASSESSED_RI function, is significant: the relevant income for MPF purposes is not stored as a static attribute on the run result but is derived according to Hong Kong statutory rules and the assignment's configuration, so the view encapsulates that derivation logic rather than requiring consumers to reproduce it.

Underlying Base Objects

The view is defined over five documented base objects: PAY_RUN_RESULTS, PAY_RUN_RESULT_VALUES, PAY_INPUT_VALUES_F, PAY_ELEMENT_TYPES_F, and the PAY_HK_PAYSLIP_ARCHIVE package. Several of these are referenced through synonyms in the APPS schema.

  • PAY_RUN_RESULTS (PRR) — the driving table. Each row represents a processed element entry for an assignment action. The view joins to it on ELEMENT_TYPE_ID and RUN_RESULT_ID.
  • PAY_RUN_RESULT_VALUES (PRRV2, PRRV3) — joined twice, to obtain the "PERIOD START DATE" and "PERIOD END DATE" input values respectively, both keyed by RUN_RESULT_ID and INPUT_VALUE_ID.
  • PAY_INPUT_VALUES_F (PIVF2, PIVF3) — joined twice, to resolve the input value names that identify the period start and end values, and to carry the LEGISLATION_CODE used to constrain the result set to Hong Kong.
  • PAY_ELEMENT_TYPES_F (PETF) — provides the element type context and restricts the view to a fixed set of MPF element names: INITIAL VALUES MPF, MPF LIABILITY, MPF WITHHELD, RETRO MPF LIABILITY, RETRO MPF WITHHELD, VOLUNTARY MPF LIABILITY, and VOLUNTARY MPF WITHHELD.
  • PAY_HK_PAYSLIP_ARCHIVE — a package, invoked as PAY_HK_PAYSLIP_ARCHIVE.GET_ASSESSED_RI(PRR.RUN_RESULT_ID) to populate ASSESSED_RI.

The join predicate PETF.LEGISLATION_CODE = 'HK' ensures only Hong Kong element types are considered.

Key Columns

  • ASSIGNMENT_ACTION_ID — identifier of the assignment action that produced the run result. Useful for grouping results by assignment and payroll action.
  • RUN_RESULT_ID — identifier of the individual run result, the row-level key of the view.
  • PERIOD_START_DATE — the MPF contribution period start, sourced from the corresponding run result value.
  • PERIOD_END_DATE — the MPF contribution period end, similarly sourced.
  • ASSESSED_RI — the assessed relevant income for the run result, computed by the Hong Kong payslip archive package function. This is the column most commonly targeted when users search for "assessed_ri".

Common Use Cases and Queries

Typical applications include preparing MPF contribution and liability reports, reconciling voluntary versus mandatory MPF amounts, and feeding data to the payslip archive process. A representative query returns the period and assessed income for a given assignment action:

  • SELECT assignment_action_id, run_result_id, period_start_date, period_end_date, assessed_ri FROM apps.pay_hk_asg_mpf_data_v WHERE assignment_action_id = :p_action_id;
  • SELECT * FROM apps.pay_hk_asg_mpf_data_v WHERE period_start_date >= :p_start AND period_end_date <= :p_end;

Because ASSESSED_RI is populated by a PL/SQL function call, queries returning many rows incur per-row function execution cost; filters on run result or assignment action should therefore be applied where possible.