Search Results pay_ie_emea_bal_v




Overview
Explore in AI Chat

Overview

PAY_IE_EMEA_BAL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Payroll (PAY) product family. Its documented purpose is narrow and specific: it supplies the balance data consumed by the Irish Payslip Advice Report. In EBS 12.1.1 and 12.2.2 the object holds status VALID, and it is not a table in its own right but a query that consolidates payroll balance definitions with their calculated values for a given assignment action.

The view is significant because payroll balances that appear on an Irish payslip advice — most notably PRSI-related figures such as the National Insurance (NI) type classification — are not stored in a single denormalized table. They are distributed across the payroll action information model and must be reassembled. PAY_IE_EMEA_BAL_V performs that reassembly, presenting one row per balance per assignment action, with a short balance name, an NI type code and a numeric value.

Underlying Base Objects

The view is defined over three referenced base objects, all exposed to APPS through synonyms:

  • PAY_ACTION_INFORMATION — aliased twice (PAI1 and PAI2). PAI1 carries the balance definition, drawn from the action information category 'EMEA BALANCE DEFINITION' with ACTION_CONTEXT_TYPE 'PA'. PAI2 carries the computed balance value, drawn from category 'EMEA BALANCES' with ACTION_CONTEXT_TYPE 'AAP'.
  • PAY_ASSIGNMENT_ACTIONS — the assignment-level action rows that tie a payroll action to an individual assignment action.
  • PAY_ACTION_INTERLOCKS — used only in the second branch of the UNION to resolve locking relationships between interlocked actions.

The definition is a UNION of two branches. The first branch joins PAI1 to PAI2 on ACTION_INFORMATION2 = ACTION_INFORMATION1, requires PAI1.ACTION_INFORMATION1 to be NULL, and links PAA1.PAYROLL_ACTION_ID to PAI1.ACTION_CONTEXT_ID. The second branch applies to the non-NULL case, introducing PAY_ACTION_INTERLOCKS to walk the locking/locked action chain so that balances produced under interlocked payroll actions are still surfaced. This dual-branch design is what allows the view to cover both directly run and lock-related payroll processing.

Key Columns

  • ASSIGNMENT_ACTION_ID — the assignment action identifier, mapped from PAI2.ACTION_CONTEXT_ID. This is the join key back to payroll processing results for a single assignment.
  • BALANCE_NAME — a 30-character truncation of PAI1.ACTION_INFORMATION4, giving the balance definition name as used on the advice report.
  • NI_TYPE — a 2-character truncation of PAI1.ACTION_INFORMATION5. This is the column most commonly searched for, and it carries the National Insurance / PRSI type classification associated with the balance. Because it is derived from the balance definition rather than the value, all values of a given balance share the same NI_TYPE.
  • BALANCE_VALUE — PAI2.ACTION_INFORMATION4 converted with TO_NUMBER, yielding the numeric balance amount. Because it is stored as action information text, implicit conversion applies; non-numeric or null content will fail or return null.

Common Use Cases and Queries

The primary use case is reproducing or troubleshooting the Irish Payslip Advice Report. A developer validating a payslip will query the view for a specific assignment action and inspect the NI type and balance value:

  • SELECT assignment_action_id, balance_name, ni_type, balance_value FROM apps.pay_ie_emea_bal_v WHERE assignment_action_id = :assignment_action_id;
  • SELECT ni_type, SUM(balance_value) FROM apps.pay_ie_emea_bal_v WHERE assignment_action_id = :assignment_action_id GROUP BY ni_type;
  • SELECT DISTINCT balance_name, ni_type FROM apps.pay_ie_emea_bal_v ORDER BY balance_name;

The last query is useful when investigating which balances carry a given NI type, since the NI_TYPE is a property of the balance definition. Because the view is read-only and its join logic depends on correctly populated EMEA balance definition and EMEA balances action information rows, missing NI_TYPE values typically indicate that the balance definition was not loaded with ACTION_INFORMATION5, or that the assignment action has not yet been processed. No DML should ever be issued against this view.