Search Results roe_payroll_reference_number

Overview

PAY_CA_ROE_VALUES_V is an APPS-owned view in the Payroll (PAY) product that exposes Canadian Record of Employment (ROE) archive data captured during payroll processing. In Oracle E-Business Suite 12.1.1 and 12.2.2, it functions as a flattened reporting layer over the assignment action and payroll action archive mechanism, decoding values that the payroll engine stores against each assignment action rather than presenting them as discrete base-table columns.

The view is a thin abstraction: it joins core payroll action and assignment action records to a utility package function that retrieves archived ROE attribute values by name. This design supports ROE reporting, reconciliation, and downstream integration, particularly where source data must be validated before submission to Service Canada or loaded into external reporting tools. The search term roe_pay_period_type maps directly to one of the archived attributes projected by this view, reflecting its role in ROE period classification reporting.

Underlying Base Objects

The documented base objects are:

• PAY_ASSIGNMENT_ACTIONS (synonym) — supplies ASSIGNMENT_ACTION_ID, ASSIGNMENT_ID, and the parent-key linkage used by archive lookups.
• PAY_PAYROLL_ACTIONS (synonym) — supplies PAYROLL_ACTION_ID, EFFECTIVE_DATE, and BUSINESS_GROUP_ID.
• PAY_CA_ARCHIVE_UTILS (package) — provides GET_ARCHIVE_VALUE, the function called repeatedly in the view's SELECT list to resolve each ROE attribute from archived assignment action data.

The view therefore performs no physical storage of ROE content itself; it derives values dynamically through the archive utility package. Because GET_ARCHIVE_VALUE is invoked for each attribute, view performance is sensitive to indexing and to the volume of archived assignment actions being queried.

Key Columns

• PAYROLL_ACTION_ID / ASSIGNMENT_ACTION_ID — keys linking the ROE line back to the payroll run and the individual assignment processed.
• EFFECTIVE_DATE — the date associated with the payroll action, often used to filter by reporting period.
• ASSIGNMENT_ID / BUSINESS_GROUP_ID — identify the employee assignment and the legislative business group.
• ROE_SERIAL_NUMBER / ROE_PREVIOUS_SERIAL_NUMBER — the ROE serial and any prior serial, used for continuity and reissue tracking.
• ROE_PAYROLL_REFERENCE_NUMBER — sourced from the archived ROE_ASG_NUMBER attribute.
• ROE_TAX_UNIT_NAME, ROE_TAX_UNIT_ADDRESS_LINE_1/2/3, ROE_TAX_UNIT_CITY, ROE_TAX_UNIT_PROVINCE, ROE_TAX_UNIT_COUNTRY, ROE_TAX_UNIT_POSTAL_CODE — the employer tax unit address block.
• ROE_CA_EMPLR_IDENT_ORG_BUS_NO — the Canada employer identification / organization business number.
• ROE_PAY_PERIOD_TYPE — the pay period type code archived against the assignment action; the specific column corresponding to the search term roe_pay_period_type.
• ROE_PER_SOCIAL_INSURANCE_NUMBR, ROE_PER_FIRST_NAME (and subsequent person-name columns truncated in the source excerpt) — employee identification attributes for the ROE.

Common Use Cases and Queries

Typical uses include validating ROE extract content, auditing pay period type assignment, and feeding ROE data to external filing processes. A representative query filtering on the pay period type is:

• SELECT assignment_action_id, roe_serial_number, roe_pay_period_type, roe_tax_unit_province FROM apps.pay_ca_roe_values_v WHERE effective_date BETWEEN :start_date AND :end_date;
• SELECT payroll_action_id, assignment_id, roe_payroll_reference_number FROM apps.pay_ca_roe_values_v WHERE business_group_id = :bg_id AND roe_pay_period_type = :period_type;
• SELECT assignment_action_id, roe_per_social_insurance_numbr, roe_tax_unit_name FROM apps.pay_ca_roe_values_v WHERE assignment_action_id = :asg_action_id;

Because each column is resolved through PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE, queries should be limited by PAYROLL_ACTION_ID, ASSIGNMENT_ACTION_ID, or EFFECTIVE_DATE to avoid full-view scans. Where performance is critical, extracting via the archive utility into a staging table is a common alternative to querying the view directly.