Results for “pay_hk_mpf_ri_balances_v”

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

Overview

PAY_HK_MPF_RI_BALANCES_V is a Hong Kong localisation view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the Payroll (PAY) product family and exposes Mandatory Provident Fund (MPF) Reimbursement/Relevant Income balance information computed for assignment-level payroll actions. The view surfaces the calculated MPF_RI balance together with its expiry date, allowing payroll administrators, HR reporting, and downstream integrations to retrieve statutory MPF balances without directly invoking the underlying PL/SQL calculation APIs.

The view is primarily a reporting and integration object. Because the MPF_RI value is derived at runtime through the PAY_HK_EXC package rather than persisted in a single fact table, direct table queries cannot easily reproduce the calculated amount. PAY_HK_MPF_RI_BALANCES_V bridges this gap by combining payroll action context, assignment context, and balance feed evidence, then returning the calculated balance for each qualifying assignment action.

Underlying Base Objects

The view is defined over several EBS synonyms and views in the APPS schema, joined to produce one row per qualifying assignment action and defined balance:

• PER_ASSIGNMENTS_F — supplies the assignment row identifier (ROW_ID) and the effective date range used to align the payroll action to a valid assignment record.
• PAY_PAYROLL_ACTIONS — provides the payroll action identifier, payroll identifier, date earned, and effective date for the run.
• PAY_ASSIGNMENT_ACTIONS — links the assignment to the specific action sequence, assignment action identifier, and tax unit.
• PAY_BALANCE_TYPES and PAY_BALANCE_TYPES_TL — filter balance types by legislation ('HK' or null) and retrieve the translated balance name.
• PAY_DEFINED_BALANCES and PAY_BALANCE_DIMENSIONS — restrict the result to the assignment-level PTD MPF dimension via DATABASE_ITEM_SUFFIX = '_ASG_MPF_PTD'.
• PAY_BALANCE_FEEDS_F, PAY_RUN_RESULTS, and PAY_RUN_RESULT_VALUES — used in an EXISTS subquery to ensure the balance type is actually fed by a run result for the assignment action.
• PAY_HK_EXC (package) — supplies CALCULATED_VALUE and GET_EXPIRY_DATE, which compute the MPF balance and its expiry date.
• HR_GENERAL and HR_SECURITY — referenced for security and utility support.

Key Columns

• ROW_ID — row identifier of the assignment record from PER_ASSIGNMENTS_F.
• PAYROLL_ID — identifier of the payroll to which the action belongs.
• ASSIGNMENT_ID — the assignment for which the MPF balance is calculated.
• ACTION_SEQUENCE and ASSIGNMENT_ACTION_ID — identify the specific assignment action row.
• BALANCE_TYPE_ID and BALANCE_NAME — the balance type and its translated name; the view is restricted to BALANCE_NAME = 'MPF_RI'.
• DEFINED_BALANCE_ID and DATABASE_ITEM_SUFFIX — identify the defined balance and its database dimension ('_ASG_MPF_PTD').
• TAX_UNIT_ID — tax unit context passed into the calculation.
• VALUE — the calculated MPF_RI balance returned by PAY_HK_EXC.CALCULATED_VALUE.
• EFFECTIVE_DATE — effective date of the payroll action.
• EXPIRY_DATE — the balance expiry date returned by PAY_HK_EXC.GET_EXPIRY_DATE.

Common Use Cases and Queries

Typical uses include MPF statutory reporting, reconciliation of calculated MPF balances against run results, and feeding downstream interfaces. A representative query retrieves the latest MPF balance for a given assignment:

• SELECT assignment_id, balance_name, value, effective_date, expiry_date FROM pay_hk_mpf_ri_balances_v WHERE assignment_id = :p_assignment_id ORDER BY effective_date DESC;
• SELECT payroll_id, assignment_id, SUM(value) FROM pay_hk_mpf_ri_balances_v WHERE effective_date BETWEEN :p_start AND :p_end GROUP BY payroll_id, assignment_id;
• SELECT * FROM pay_hk_mpf_ri_balances_v WHERE assignment_action_id = :p_assignment_action_id;

Because the view invokes PAY_HK_EXC functions per row, queries should be filtered tightly by assignment, payroll action, or effective date to avoid full scans and repeated calculation overhead. The view is read-only and should not be used as a target for DML.