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:
- Employee identity — CAEOY_EMPLOYEE_SIN, CAEOY_EMPLOYEE_LAST_NAME, CAEOY_EMPLOYEE_FIRST_NAME, CAEOY_EMPLOYEE_INITIAL, CAEOY_PERSON_ID, and CAEOY_T4_EMPLOYEE_REGISTRATION_NO.
- Address block — CAEOY_EMPLOYEE_ADDRESS_LINE1 through LINE3, CITY, PROVINCE, COUNTRY and POSTAL_CODE, used for the employee mailing address required on the T4.
- Exemption and jurisdiction flags — CAEOY_CPP_QPP_EXEMPT and CAEOY_EI_EXEMPT, each resolved against the JURISDICTION_CODE context; CAEOY_EMPLOYMENT_CODE carries the employment type.
- Organizational keys — PPA.BUSINESS_GROUP_ID, PAA.TAX_UNIT_ID and PPA.PAYROLL_ACTION_ID, plus the raw FAI.CONTEXT1 and FAC.CONTEXT identifiers.
- Financial amounts — CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD and related jurisdiction-scoped measures expressed as year-to-date values.
- T4 placement attributes — CAEOY_T4_ORGANIZATION_ID and CAEOY_T4_LOCATION_ID.
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.
-
View: PAY_CA_EOY_REGISTER_V 12.1.1
APPS.PAY_CA_EOY_REGISTER_V·↳ FF_ARCHIVE_ITEMS·↳ FF_ARCHIVE_ITEM_CONTEXTS·↳ FF_CONTEXTS·Explore PAY module →
-
View: PAY_CA_EOY_REGISTER_V 12.2.2
APPS.PAY_CA_EOY_REGISTER_V·↳ FF_ARCHIVE_ITEMS·↳ FF_ARCHIVE_ITEM_CONTEXTS·↳ FF_CONTEXTS·Explore PAY module →
-
SYNONYM: APPS.FF_CONTEXTS 12.2.2
-
SYNONYM: APPS.FF_CONTEXTS 12.1.1
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.2.2 FND Design Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.