Search Results roe_emp_per_hire_date

Overview

APPS.PAY_CA_ROE_VALUES_V is a supplementary view owned by the APPS schema in Oracle E-Business Suite, registered under FND Design Data as PAY.PAY_CA_ROE_VALUES_V. Its status is VALID. The view supports the Canadian Record of Employment (ROE) process, exposing the individual data elements that the ROE form requires for a given assignment action, payroll action, and effective date. As a supplementary view used to simplify Oracle Forms coding, Oracle explicitly warns that it is not intended for direct querying or data alteration and that its definition may change dramatically between releases. The view therefore serves as a presentation and integration layer rather than a permanent public interface; report developers should treat the column set as descriptive of the current release only.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects for this view: PAY_ASSIGNMENT_ACTIONS (SYNONYM), PAY_CA_ARCHIVE_UTILS (PACKAGE), and PAY_PAYROLL_ACTIONS (SYNONYM). The two synonyms resolve to the assignment-action and payroll-action tables that anchor the view to a specific payroll run and a specific assignment action. The inclusion of PAY_CA_ARCHIVE_UTILS, a Canadian legislative/archiving utility package, indicates that a significant portion of the ROE attribute derivation is performed through PL/SQL functions inside that package rather than through a purely declarative join. Consequently the view functions more as a callable envelope of payroll and assignment-action keys than as a conventional relational projection, and any query against it implicitly invokes that package logic.

Key Columns

The view carries identifying keys—PAYROLL_ACTION_ID, ASSIGNMENT_ACTION_ID, ASSIGNMENT_ID, BUSINESS_GROUP_ID, and EFFECTIVE_DATE—followed by a very large set of VARCHAR2(4000) ROE attributes. Among these is ROE_CA_EMPLR_IDENT_ORG_BUS_NO, the employer's Canada Revenue Agency business number, the element referenced in the originating search. Related employer and tax-unit attributes include ROE_TAX_UNIT_NAME, ROE_TAX_UNIT_ADDRESS_LINE_1 through 3, ROE_TAX_UNIT_CITY, ROE_TAX_UNIT_PROVINCE, ROE_TAX_UNIT_COUNTRY, and ROE_TAX_UNIT_POSTAL_CODE. Serialization and payroll linkage columns include ROE_SERIAL_NUMBER, ROE_PREVIOUS_SERIAL_NUMBER, and ROE_PAYROLL_REFERENCE_NUMBER. Employee-level attributes include ROE_PER_SOCIAL_INSURANCE_NUMBR, ROE_PER_FIRST_NAME, ROE_PER_LAST_NAME, the ROE_PER_ADDRESS_LINE_1 through 3 columns, ROE_PER_CITY, ROE_PER_PROVINCE, ROE_PER_COUNTRY, and ROE_PER_POSTAL_CODE. Employment and earnings data are carried by ROE_EMP_PER_HIRE_DATE, ROE_PAY_EARNED_END_DATE, ROE_FINAL_PAY_PERIOD_END_DATE, ROE_ASG_JOB, ROE_UNKNOWN_NOT_RETURNING, ROE_EXPECTED_DATE_OF_RECALL, ROE_TOTAL_INSURABLE_HOURS, ROE_TOTAL_INSURABLE_EARNINGS, and the block ROE_INSURABLE_EARNING_1 through ROE_INSURABLE_EARNING_5. All displayoriented values are surfaced as character strings, so date and numeric values require explicit conversion when consumed programmatically.

Common Use Cases and Queries

The primary scenario is extraction of ROE form values for a completed payroll action, either for printing the ROE or for transmitting insurable-earnings data to Service Canada. A typical query filters on the payroll action and assignment action keys.

• SELECT roe_ca_emplr_ident_org_bus_no, roe_per_last_name, roe_per_social_insurance_numbr, roe_total_insurable_earnings FROM apps.pay_ca_roe_values_v WHERE payroll_action_id = :p_payroll_action_id AND assignment_action_id = :p_assignment_action_id;
• SELECT assignment_id, roe_serial_number, roe_payroll_reference_number, roe_pay_earned_end_date FROM apps.pay_ca_roe_values_v WHERE payroll_action_id = :p_payroll_action_id ORDER BY assignment_id;
• SELECT roe_insurable_earning_1, roe_insurable_earning_2, roe_insurable_earning_3, roe_insurable_earning_4, roe_insurable_earning_5 FROM apps.pay_ca_roe_values_v WHERE assignment_action_id = :p_assignment_action_id;

Because the view is documented as supplementary and subject to change, integrations should be re-validated after each upgrade, and the returned character values should be converted to the appropriate native datatypes before downstream use.