Results for “class_a_insurable_weeks”

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

Overview

The PAY_IE_P45_PRSI_DETAILS view is an Oracle E-Business Suite database object owned by the APPS schema within the Payroll (PAY) product family. As documented in the ETRM metadata, its purpose is to support the Irish P45 form by populating Pay-Related Social Insurance (PRSI) details for terminated employees. The view is classified as VALID and forms part of the Irish localization (IE) reporting infrastructure, specifically the P45 cessation-of-employment document.

The view does not store data itself; it is a presentation-layer construct that consolidates PRSI contribution amounts and insurable week counts derived from archived payroll balances. It is consumed by the P45 form generation process, which requires employer and employee PRSI totals along with associated insurable weeks data for each assignment action belonging to a P45 payroll action. The view abstracts the underlying archive retrieval logic, exposing pre-computed columns such as TOTAL_EMPLOYER_PRSI, TOTAL_EMPLOYEE_PRSI, INSURABLE_WEEKS, CLASS_A_INSURABLE_WEEKS, and OTHER_INSURABLE_WEEKS. Because these values are sourced from archived results rather than live balances, the view is appropriate for historical or termination reporting where payroll runs have already been processed and archived.

Underlying Base Objects

According to the documented view metadata, PAY_IE_P45_PRSI_DETAILS is defined over the following base objects:

• PAY_ASSIGNMENT_ACTIONS (SYNONYM) — aliased as PAA; supplies the ASSIGNMENT_ACTION_ID and PAYROLL_ACTION_ID join keys.
• PAY_PAYROLL_ACTIONS (SYNONYM) — aliased as PPA; restricts results to P45 actions through the predicates REPORT_TYPE = 'P45', REPORT_QUALIFIER = 'IE', REPORT_CATEGORY = 'ARCHIVE', and ACTION_STATUS = 'C'.
• PAY_ACTION_INFORMATION (SYNONYM) — aliased as PAI_IEED; provides the ACTION_INFORMATION22 field, from which OTHER_INSURABLE_WEEKS is derived via SUBSTR(..., 1, 40), and enables joins on ACTION_CONTEXT_ID and EFFECTIVE_DATE.
• PAY_IE_P45_ARCHIVE (PACKAGE) — the PL/SQL package whose GET_ARC_BAL_VALUE function retrieves archived balance values for each named PRSI balance.

The joins tie assignment actions to payroll actions on PAYROLL_ACTION_ID, and to action information on ACTION_CONTEXT_ID and EFFECTIVE_DATE, ensuring that archived PRSI figures align with the correct P45 reporting run.

Key Columns

• ASSIGNMENT_ACTION_ID — Identifier for the assignment-level action; the principal row key.
• TOTAL_EMPLOYER_PRSI — Sum of employer PRSI balances: 'IE PRSI EMPLOYER', 'IE PRSI K EMPLOYER LUMP SUM', and 'IE PRSI M EMPLOYER LUMP SUM', each defaulted to zero via NVL.
• TOTAL_EMPLOYEE_PRSI — Sum of employee PRSI balances: 'IE PRSI EMPLOYEE', 'IE PRSI K EMPLOYEE LUMP SUM', and 'IE PRSI M EMPLOYEE LUMP SUM'.
• INSURABLE_WEEKS — Sum of 'IE PRSI INSURABLE WEEKS', 'IE PRSI K TERM INSURABLE WEEKS', and 'IE PRSI M TERM INSURABLE WEEKS'.
• CLASS_A_INSURABLE_WEEKS — Value of the 'IE PRSI_CLASSA INSURABLE WEEKS' balance.
• OTHER_INSURABLE_WEEKS — First 40 characters of ACTION_INFORMATION22 from PAY_ACTION_INFORMATION.

Common Use Cases and Queries

The view is used primarily where a user or process needs to retrieve consolidated PRSI figures for P45 reporting, particularly where the search term "total_employer_prsi" applies. Typical queries filter by assignment action or payroll action to obtain the employer PRSI total for a terminated employee.

• Extract employer and employee PRSI for a specific assignment action.
• Reconcile insurable weeks reported on the P45 against archived payroll balances.
• Feed downstream Irish statutory reporting or data extracts for leavers.

Sample query:

SELECT ASSIGNMENT_ACTION_ID,
      TOTAL_EMPLOYER_PRSI,
      TOTAL_EMPLOYEE_PRSI,
      INSURABLE_WEEKS,
      CLASS_A_INSURABLE_WEEKS,
      OTHER_INSURABLE_WEEKS
FROM  APPS.PAY_IE_P45_PRSI_DETAILS
WHERE  ASSIGNMENT_ACTION_ID = :p_assignment_action_id;

Because the underlying function reads archived balances, performance is influenced by the volume of archived P45 actions, and the view returns rows only for completed (ACTION_STATUS = 'C') Irish P45 archive runs.