Results for “sit_exemptions”

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

Overview

HR_CERIDIAN_SECONDARY_STATE_V is a VALID view owned by the APPS schema within the PER (Human Resources) product module of Oracle E-Business Suite. As documented in the ETRM metadata, its stated purpose is to serve as a "Ceridian Source 100 payroll interface view." Historically, Ceridian Source 100 was a third-party US payroll service bureau platform, and Oracle EBS shipped a set of integration views under the HR_CERIDIAN prefix to expose the subset of HR and payroll-tax data required for that outbound interface. HR_CERIDIAN_SECONDARY_STATE_V represents the state and local "secondary" tax-withholding segment of that feed, complementing the primary/federal tax views in the same family.

Because it is a reporting and interface-facing object rather than a transactional table, the view is read-only and consolidates data from the person, assignment, location, and payroll tax-rule models into a flattened, payroll-bureau-friendly row shape. No PL/SQL logic is applied; all transformation occurs through pure SELECT and DECODE expressions.

Underlying Base Objects

The view is defined as a join across the following documented base objects (all exposed in the APPS schema via synonyms):

• PER_ALL_PEOPLE_F — person/employee master, supplying EMPLOYEE_NUMBER, names, BUSINESS_GROUP_ID, and PERSON_ID.
• PER_ALL_ASSIGNMENTS_F — assignments, filtering to employee assignments (ASSIGNMENT_TYPE = 'E') and joining to soft coding keyflex.
• HR_ALL_ORGANIZATION_UNITS — the business group/organization name, joined via SEGMENT1 of the soft coding keyflex.
• HR_SOFT_CODING_KEYFLEX — links the assignment to the organization unit ID.
• HR_LOCATIONS_ALL — location data (STYLE = 'US'), providing LOC_INFORMATION17 and REGION_2 as the effective state code candidate.
• PAY_US_EMP_STATE_TAX_RULES_F — employee state income tax (SIT) election and override details.
• PAY_US_EMP_CITY_TAX_RULES_F — employee local income tax (LIT) election and override details.
• PAY_US_CITY_NAMES, PAY_US_COUNTIES, PAY_US_STATES — reference/validation tables resolving city names, counties, and state abbreviations.

Key Columns

• EMPLOYEE_NUMBER, LAST_NAME, FIRST_NAME, MIDDLE_NAMES — employee identification for the payroll bureau.
• Organization NAME — business group / legal employer designation.
• NVL(LOC_INFORMATION17, REGION_2) — the effective state code, falling back to region when the flexfield segment is null.
• JURISDICTION_CODE — the local tax jurisdiction code (e.g., city/county authority).
• CITY_NAME — selected twice in the SELECT list, corresponding to the two city-name joins.
• SIT_EXEMPT / LIT_EXEMPT — exempt flags, defaulted to 'N'.
• WITHHOLDING_ALLOWANCES — number of state withholding allowances.
• Override expressions — DECODE chains composing SIT/LIT override amounts, rates (with '%'), and additional tax (with '$') into display strings.
• BUSINESS_GROUP_ID, PERSON_ID — surrogate keys for downstream joins.
• LAST_UPDATE_DATE — the GREATEST of the location, SIT, LIT, and assignment update dates, used for cell/incremental extraction.

Common Use Cases and Queries

The view is typically consumed by the Ceridian interface to extract secondary state/local withholding elections per active employee assignment. A simple extraction looks like:

• SELECT employee_number, last_name, first_name, jurisdiction_code, sit_exempt, lit_exempt FROM apps.hr_ceridian_secondary_state_v;
• Filtering to a business group: ... WHERE business_group_id = :p_bg_id;
• Incremental pulls using the derived timestamp: ... WHERE last_update_date > :last_run_date;
• Joining back to PER_ALL_ASSIGNMENTS_F or PER_ALL_PEOPLE_F on PERSON_ID when additional attributes are required for the interface file.

Because it aggregates tax rules and location data, the view is also useful for ad-hoc US state/local withholding audits, though production reporting is normally directed against the underlying PAY_US tables to avoid duplicate CITY_NAME columns and Cartesian risk when persons hold multiple assignments.