Results for “hr_ceridian_assignment_v”

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

Overview

HR_CERIDIAN_ASSIGNMENT_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite, classified under the PER (Human Resources) product family. It is documented by ETRM as the "Ceridian Source 100 payroll interface view," a legacy integration artifact that extracts the assignment-, pay-, and personal-attribute data required by the Ceridian Source 100 outsourced payroll service. In EBS 12.1.1 and 12.2.2 the view retains its VALID status and remains available to integrators and report developers, although it is no longer the primary payroll interface for current Ceridian offerings.

The view presents a single denormalized row per employee-assignment combination, joining person, address, pay basis, time-period, soft-coding, payroll, and assignment-status data. Its most distinctive construction is a series of GREATEST(NVL(...LAST_UPDATE_DATE...), ...) expressions, which compute the effective last-update timestamp across the contributing entities. This is directly relevant to the user's search term sal_last_update_date: the view does not expose a literal column by that name, but it manufactures SAL-style last-update stamps used for incremental extraction and synchronization with Ceridian.

Underlying Base Objects

ETRM 12.2.2 documents HR_CERIDIAN_ASSIGNMENT_V as referencing the following base objects: HR_GENERAL (package), HR_LOCATIONS_ALL (synonym), HR_ORGANIZATION_UNITS (view), HR_SECURITY (package), HR_SOFT_CODING_KEYFLEX (synonym), PAY_COST_ALLOCATIONS_F (synonym), PAY_COST_ALLOCATION_KEYFLEX (synonym), PAY_ELEMENT_ENTRIES_F (synonym), PAY_ELEMENT_ENTRY_VALUES_F (synonym), PAY_ELEMENT_LINKS_X (view), PAY_ELEMENT_TYPES_X (view), PAY_INPUT_VALUES_F (synonym), PAY_JOB_WC_CODE_USAGES (synonym), PAY_PAYROLLS_X (view), PER_ADDRESSES (synonym), PER_ALL_ASSIGNMENTS_F (synonym), PER_ALL_PEOPLE_F (synonym), PER_ASSIGNMENT_STATUS_TYPES (synonym), PER_ASS_STATUS_TYPE_AMENDS (synonym), PER_PAY_BASES (synonym), PER_PERIODS_OF_SERVICE (synonym), and PER_TIME_PERIOD_TYPES (synonym).

The view text confirms these dependencies: dates and identifiers come from PER_ALL_PEOPLE_F (PP), PER_ADDRESSES (PA), PER_PERIODS_OF_SERVICE (PPS), PER_PAY_BASES (PPB), PER_TIME_PERIOD_TYPES (PTP), and PER_ASSIGNMENT_STATUS_TYPES/PER_ASS_STATUS_TYPE_AMENDS (AST/ASTA). Payroll attributes are drawn from PAY_ELEMENT_ENTRY_VALUES_F (PBEV), PAY_ELEMENT_ENTRIES_F (PBEE), and PAY_INPUT_VALUES_F (PPIV), while HR_ORGANIZATION_UNITS (HOU) provides the organization name and HR_SOFT_CODING_KEYFLEX (HS) supplies segments such as SEGMENT5 and SEGMENT8. Many of these objects are themselves views or synonyms layered over the underlying _F (date-tracked) and _X (translated) tables, so the view must be queried with the effective-date semantics of the HRMS DateTrack model in mind.

Key Columns

• EMPLOYEE_NUMBER, LAST_NAME, FIRST_NAME, MIDDLE_NAMES — employee identifiers and name components from PER_ALL_PEOPLE_F.
• NAME — organization name from HR_ORGANIZATION_UNITS.
• DATE_START, FINAL_PROCESS_DATE — period-of-service and payroll final-process dates from PER_PERIODS_OF_SERVICE.
• NVL(HS.SEGMENT8, PJW.WC_CODE) — workers' compensation code, resolved from soft coding first.
• DECODE(PTP.NUMBER_PER_FISCAL_YEAR, ...) — pay frequency indicator (W, S, B, M) derived from the time-period type.
• DECODE(PPB.PAY_BASIS, 'HOURLY'/'ANNUAL', PBEV.SCREEN_ENTRY_VALUE, NULL) — separate hourly and annual rate columns returned as ERROR for unexpected bases.
• NVL(ASTA.PAY_SYSTEM_STATUS, AST.PAY_SYSTEM_STATUS) — effective pay-system status, honoring status amendments.
• PJ.NORMAL_HOURS — standard hours from the payroll definition.
• PCA.SEGMENT1||SEGMENT2||SEGMENT3 — concatenated cost-allocation key flexfield.
• HS.SEGMENT5 — additional soft-coding segment.
• BUSINESS_GROUP_ID, PERSON_ID — keys for joining back to HRMS and for multi-organization (business group) filtering.
• GREATEST(NVL(...LAST_UPDATE_DATE...), ...) — derived last-update timestamps per source entity. The highest of these effectively serves as the assignment's sal_last_update_date for delta extraction; the final expression rolls up PPS, PJW, HL, AST, and ASTA update dates.

Common Use Cases and Queries

Typical uses are: feeding the legacy Ceridian Source 100 payroll extract, auditing which employees changed since a prior run, and validating pay rates and workers' compensation codes before transmission. Because the last-update logic is embedded in the view, incremental pulls compare the derived timestamp to a stored high-water mark rather than filtering a literal SAL_UPD_DATE column.

SELECT employee_number,
       last_name,
       first_name,
       date_start,
       normal_hours,
       business_group_id,
       person_id
FROM   apps.hr_ceridian_assignment_v
WHERE  business_group_id = :p_bg_id
ORDER  BY last_name, first_name;

/* Incremental extraction using the derived last-update stamp */
SELECT v.employee_number, v.person_id, v.name
FROM   apps.hr_ceridian_assignment_v v
WHERE  v.business_group_id = :p_bg_id
AND    ( SELECT MAX(x.last_update_stamp)
         FROM   ( SELECT GREATEST(NVL(pp.last_update_date, TO_DATE('01-01-1900','DD-MM-YYYY')),
                                  pp.effective_start_date) AS last_update_stamp
                  FROM   apps.per_all_people_f pp
                  WHERE  pp.person_id = v.person_id ) x ) >= :last_run_date;

When adapting the view for current integrations, consult HR_SECURITY and HR_GENERAL because the underlying view text invokes these packages to enforce security and business-group context; querying without proper responsibility context can return incomplete rows. As with all DateTracked HRMS objects, specify an effective date when joining directly to the _F tables, and treat the GREATEST expressions as the authoritative change indicator for synchronization.