Search Results emp_code

Overview

The HR_CERIDIAN_500_ASSIGNMENT_V view is an Oracle E-Business Suite database object owned by the APPS schema and validated in releases 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and is documented as a "Ceridian Source 500 payroll interface view." Its purpose is to expose a flattened, payroll-ready projection of employee assignment and element entry data formatted specifically for consumption by the Ceridian Source 500 third-party payroll interface.

The view does not store data; it is a query construct layered over core Oracle Payroll and Human Resources tables. It performs column mapping, DECODE-based code translation, hot-default resolution, and date effective-range reconciliation so that an external payroll processor receives assignment records in the expected Ceridian layout. Because the view joins people, assignments, element links, input values, and element entry values, it is principally used in outbound integration extracts rather than interactive HR reporting.

Underlying Base Objects

The view is defined over a broad set of base and interface objects, as recorded in the ETRM metadata. Core HR objects include PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_PERIODS_OF_SERVICE, PER_PAY_BASES, PER_PAY_PROPOSALS, PER_ASSIGNMENT_STATUS_TYPES, PER_ASS_STATUS_TYPE_AMENDS, and PER_TIME_PERIOD_TYPES. Payroll-specific sources include PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_ENTRY_VALUES_F, PAY_ELEMENT_LINKS_F, PAY_ELEMENT_LINKS_X, PAY_ELEMENT_TYPES_F, PAY_INPUT_VALUES_F, PAY_PAYROLLS_F, and PAY_JOB_WC_CODE_USAGES. Supporting organizational and security objects include HR_LOCATIONS_ALL, HR_ORGANIZATION_UNITS, HR_SOFT_CODING_KEYFLEX, and HR_SECURITY. Three packages are invoked directly from the view text: HR_CERIDIAN, HR_GENERAL, and HR_PAY_INTERFACE_PKG.

A distinguishing characteristic of the definition is the use of an ordered hint with four explicit NO_INDEX directives targeting PAY_ELEMENT_ENTRY_VALUES_F_N1. This index name corresponds directly to element-entry value retrieval, which is precisely the term a user would reference when searching for pay_element_entry_values_f_n1. The hint forces the optimizer away from that index, steering the plan toward alternative access paths typical of bulk extract operations.

Key Columns

The view exposes an assignment-centric payload. Identification columns include EMPLOYEE_NUMBER (from PER_ALL_PEOPLE_F), ASSIGNMENT_NUMBER and PRIMARY_FLAG (from assignment and pay proposal data), and NATIONAL_IDENTIFIER. Effective dating is presented through EFFECTIVE_START_DATE and a computed end-date expression that compares the Ceridian extract date — returned by HR_CERIDIAN.GET_CER_EXTRACT_DATE — against the assignment's effective end date, emitting a null date when the extract falls within the active range.

Four element-entry value columns are produced using nested DECODE logic. Each evaluates the input value's HOT_DEFAULT_FLAG; when the flag is not 'N' and the screen entry value is null, the value is sourced from HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT using the input value identifier and element link identifier. Results are truncated to sixty characters. WORKSITE_NUMBER and WORKSITE_STATE_CODE are projected as blank-padded literals. RATE_CODE is derived from PER_PAY_BASES.PAY_BASIS, mapping HOURLY to '4' and ANNUAL, MONTHLY, or PERIOD to '2'. Salary amounts are subsequently normalized using PER_TIME_PERIOD_TYPES.NUMBER_PER_FISCAL_YEAR.

Common Use Cases and Queries

Typical usage centers on extract generation and reconciliation of Ceridian-bound assignment data. A basic query can be issued as follows:

• SELECT employee_number, assignment_number, primary_flag, rate_code FROM apps.hr_ceridian_500_assignment_v; — returns the identification and rate mapping columns.
• SELECT employee_number, effective_start_date FROM apps.hr_ceridian_500_assignment_v WHERE primary_flag = 'Y'; — isolates primary assignments for a given extract run.
• SELECT * FROM apps.hr_ceridian_500_assignment_v WHERE employee_number = :p_employee_number; — retrieves the full Ceridian payload for a single employee.

Because the view calls HR_SECURITY and the HR_CERIDIAN package, query performance and row visibility depend on security profile configuration and on the extract date returned by GET_CER_EXTRACT_DATE. The embedded NO_INDEX hints mean that execution plans will favor full scans or alternative joins on PAY_ELEMENT_ENTRY_VALUES_F; DBAs tuning extract performance should therefore examine the plan rather than assume the _N1 index is used. The view is best treated as an integration artifact for the Ceridian Source 500 interface, not as a general-purpose HR reporting source.