Results for “person_effective_start_date”

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

Overview

PAY_CA_SOE_EMPINFO_V is an APPS-owned database view in the Oracle E-Business Suite Payroll (PAY) product, delivered for Canadian Statutory/Statement of Earnings (SOE) reporting requirements. The view consolidates person, assignment, payroll action, payment, tax unit, location, and time period information into a single flattened rowset suitable for Canadian Record of Employment (ROE) and related SOE style reporting. Because it is a view rather than a table, it carries no independent storage and is always resolved against its underlying base objects at runtime.

The view is defined with DISTINCT, so duplicate person/action/payment combinations sourced from the joins across person, assignment, and payroll action tables are collapsed. Several address and salary attributes are not stored columns but function call results, produced through the PAY_CA_EMP_TAX_INF package. This makes the view effectively read-only and unsuitable as a target for DML; it is intended for query and extract purposes.

The user search term "person_effective_end_date" is not present as a literal column in the view text provided. Related date columns available are PTP.START_DATE, PTP.END_DATE sourced from the time periods object, and PPA.EFFECTIVE_DATE / PPA.DATE_EARNED from the payroll action. Date-bounded person attributes would typically be sourced from PER_PEOPLE_F and its EFFECTIVE_END_DATE column, which participates as a referenced base object in the view definition.

Underlying Base Objects

The documented base objects referenced by PAY_CA_SOE_EMPINFO_V include a mix of tables, synonyms, views, and packages:

• PER_PEOPLE_F and PER_ASSIGNMENTS_F — effective-dated person and assignment views (the natural home of effective start/end date attributes).
• PER_TIME_PERIODS — payroll time period definitions providing START_DATE and END_DATE.
• PAY_ASSIGNMENT_ACTIONS, PAY_PAYROLL_ACTIONS, PAY_PRE_PAYMENTS — payroll action and payment processing data.
• HR_LOCATIONS, HR_ORGANIZATION_UNITS, HR_SOFT_CODING_KEYFLEX — location, organization, and flexfield sources.
• HR_GENERAL, HR_PERSON_NAME, HR_SECURITY — packages supplying person name and security predicates.
• PAY_CA_EMP_TAX_INF — Canadian employee tax information package providing GET_ADDRESS and GET_BASE_SALARY functions.

These are joined to produce one row per person/assignment/payroll action/payment combination. The view logically sits on top of the core HR and Payroll model, so it inherits the effective-dating behavior of PER_PEOPLE_F and PER_ASSIGNMENTS_F.

Key Columns

• PERSON_ID, FULL_NAME, NATIONAL_IDENTIFIER, EMPLOYEE_NUMBER — person identification attributes.
• EMP_ADD_LN1 through EMP_ADD_LN4 — formatted employee address lines returned by PAY_CA_EMP_TAX_INF.GET_ADDRESS.
• PAYROLL_ID, ASSIGNMENT_NUMBER, NORMAL_HOURS, FREQUENCY — assignment and payroll attributes.
• ACTION_TYPE, EFFECTIVE_DATE, DATE_EARNED, ACTION_STATUS — payroll action detail; ACTION_TYPE drives the DECODE used for address and salary date selection.
• ASSIGNMENT_ID, PAYROLL_ACTION_ID, ASSIGNMENT_ACTION_ID, PRE_PAYMENT_ID, ACTION_SEQUENCE, CHUNK_NUMBER, SERIAL_NUMBER, OBJECT_VERSION_NUMBER — action and payment linkage keys.
• TAX_UNIT_ID, NAME, ORGANIZATION_ID, LOCATION_ID, LOCATION_CODE — tax unit, organization, and location identifiers.
• WORK_SCHEDULE_ID, START_DATE, END_DATE — work schedule and time period boundaries.
• GET_BASE_SALARY result — base salary for the assignment at the action-appropriate date.

Common Use Cases and Queries

Typical usage includes Canadian ROE/SOE extract generation, payroll-to-payment reconciliation, and audit of tax unit or location changes by payroll action. A representative query restricting to a date window using the available date columns is shown below:

SELECT person_id,
       full_name,
       employee_number,
       assignment_number,
       action_type,
       effective_date,
       date_earned,
       start_date,
       end_date
FROM   apps.pay_ca_soe_empinfo_v
WHERE  effective_date BETWEEN :p_from AND :p_to
ORDER BY person_id, effective_date;

For locating effective end dates, joins to PER_PEOPLE_F carried alongside the view are required, since the view does not expose a person_effective_end_date column directly. Analysts searching for that term should filter on PER_PEOPLE_F.EFFECTIVE_END_DATE and correlate by PERSON_ID.