Results for “pay_ca_eoy_register_v”

26 results




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

Overview

PAY_CA_EOY_REGISTER_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) product. It exposes data required to produce the Canadian End-of-Year (EOY) register, the statutory reporting artifact employers must file after each payroll year to report employment income, Canada Pension Plan (CPP), Quebec Pension Plan (QPP), Employment Insurance (EI) and related deductions. The view is marked VALID and is documented in ETRM for both 12.1.1 and 12.2.2. Its role is to transform archived, context-tagged payroll values into a flat, presentation-ready result set that a concurrent program, BI Publisher report or custom extract can consume without directly parsing the underlying archive structures.

Unlike conventional payroll views that join to live assignment and element tables, this view reads historical values that have already been persisted by the EOY archive process. This design decouples year-end reporting from ongoing payroll processing, ensuring the register is reproducible even after subsequent payroll runs, corrections or purges alter current data.

Underlying Base Objects

The view is defined over four archived-object synonyms and one PL/SQL package, all resident in APPS:

  • FF_ARCHIVE_ITEMS — the archive item definitions that identify each tracked value (for example employee SIN, employment code, address attributes).
  • FF_ARCHIVE_ITEM_CONTEXTS — the context values that qualify each archive item, such as jurisdiction code.
  • FF_CONTEXTS — the context dictionary used to resolve context names introduced by the archive structures.
  • PAY_ASSIGNMENT_ACTIONS — links the archived data to the payroll action and assignment that produced it.
  • PAY_PAYROLL_ACTIONS — supplies the payroll action, tax unit and business group identifiers.
  • PAY_CA_ARCHIVE_UTILS — a PL/SQL package whose GET_ARCHIVE_VALUE function performs the actual retrieval. The view projects one call to this function per output column, passing FAI.CONTEXT1 (the archive item context) and, where relevant, FAC.CONTEXT together with a context type such as 'JURISDICTION_CODE'.

The repeated use of GET_ARCHIVE_VALUE is the defining characteristic of this view: it converts the archive's name/value storage model into named relational columns.

Key Columns

The SELECT list exposes a mixture of resolved archive values and direct foreign keys:

Common Use Cases and Queries

Typical use cases include validating the EOY archive before filing, reconciling T4 slips to payroll history, and extracting SIN and address details for slip generation. Because the view is read-intensive and calls a PL/SQL function per column, queries should constrain by payroll action or business group rather than scanning the full view.

SELECT ca_eoy_employee_sin,
       ca_eoy_employee_last_name,
       ca_eoy_employee_first_name,
       ca_eoy_cpp_qpp_exempt,
       ca_eoy_ei_exempt,
       ca_eoy_gross_earnings_per_jd_gre_ytd
FROM   apps.pay_ca_eoy_register_v
WHERE  business_group_id  = :p_business_group_id
AND    payroll_action_id = :p_payroll_action_id
ORDER  BY ca_eoy_employee_last_name, ca_eoy_employee_first_name;

A second common pattern joins the view back to PAY_PAYROLL_ACTIONS to confirm the action and date range being reported, and to PAY_ASSIGNMENT_ACTIONS to align results with assignment-level processing records.