Results for “sit_effective_end_date”

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

Overview

HR_ADP_RESIDENT_STATE_TAX_V is a VALID database view owned by the APPS schema in Oracle E-Business Suite (12.1.1 / 12.2.2). It is classified under the PER – Human Resources product module and is described in the ETRM metadata as an ADP payroll interface view. Its purpose is to collect and present resident state tax information — the tax withholding data associated with an employee's state of residence — in a flat, denormalized form suitable for extraction by ADP payroll processing interfaces.

Because the object is a view and not a table, it stores no data itself. Instead it dynamically joins person, assignment, period-of-service, location, and multiple US state and federal tax rule tables, applying HR security and decoding lookup values into ADP-friendly codes. It is typically consumed by concurrent programs, extracts, and outbound interfaces that transmit employee tax setup data to an external ADP payroll provider.

Underlying Base Objects

The view is defined over a substantial set of APPS synonyms and package dependencies. The documented referenced base objects include:

• PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_ADDRESSES, PER_PERIODS_OF_SERVICE, PER_TIME_PERIOD_TYPES — the PER core person, assignment, address, and payroll period sources.
• PAY_US_EMP_STATE_TAX_RULES_F, PAY_US_STATE_TAX_INFO_F, PAY_US_EMP_FED_TAX_RULES_F, PAY_US_EMP_CITY_TAX_RULES_F, PAY_STATE_RULES, PAY_US_STATES, PAY_US_CITY_NAMES, PAY_US_CITY_TAX_INFO_F, PAY_US_COUNTIES — the US payroll and taxation rule tables.
• HR_ALL_ORGANIZATION_UNITS, HR_LOCATIONS_ALL, HR_SOFT_CODING_KEYFLEX, HR_LOOKUPS — organizational, location, key flexfield, and lookup sources.
• HR_ADP (PACKAGE), HR_ADP_EMP_REF_V (VIEW), HR_API, HR_GENERAL, HR_SECURITY (PACKAGES) — supporting HR logic, security, and ADP reference data.

The view text applies an ORDERED and PUSH_SUBQ hint with a comment noting a known CBO bug in Oracle 8.1.6 and recommending re-evaluation of the hints for 9i environments. The join strategy emphasizes filtering rows early and forcing table order for predictable performance.

Key Columns

The columns exposed by the view map directly to the resident state tax attributes needed by ADP. Principal columns include:

• COMPANY_CODE_EQUIVALENT — derived from the soft coding key flexfield, representing the ADP company code.
• GRE_NAME, GRE_ID — the GRE (Group Reporting Entity) name and segment, sourced from HR_ALL_ORGANIZATION_UNITS and HR_SOFT_CODING_KEYFLEX.
• ORGANIZATION_ID, BUSINESS_GROUP_ID, PERSON_ID, EMPLOYEE_NUMBER — identifying keys for the employee's organizational and HR context.
• PAY_FREQUENCY — decoded from PER_TIME_PERIOD_TYPES.NUMBER_PER_FISCAL_YEAR into ADP codes (W, S, B, M).
• ASSIGNMENT_ID, ASSIGNMENT_NUMBER, ASSIGNMENT_SEQUENCE, PRIMARY_FLAG — assignment-level identifiers.
• FIT_EFFECTIVE_START_DATE, FIT_EFFECTIVE_END_DATE, FIT_FILING_STATUS, FIT_FILING_STATUS_CODE — federal income tax filing status and its decoded equivalent (S, M, H, F).
• FIT_EXEMPTIONS, FIT_OVERRIDE_AMOUNT, FIT_OVERRIDE_RATE, FIT_ADDITIONAL_TAX, FIT_EXEMPT_FLAG, FIT_SUPP_OVERRIDE_RATE — federal withholding exemptions, overrides, add-ons, and exempt flags.
• EIC_FILING_STATUS, STATUTORY_EMPLOYEE_FLAG, MEDICARE_EXEMPT_FLAG, FUTA_EXEMPT_FLAG — Earned Income Credit status and exemption indicators.

These columns are drawn primarily from PAY_US_EMP_STATE_TAX_RULES_F and PAY_US_EMP_FED_TAX_RULES_F, decoded via HR_LOOKUPS and DECODE expressions to match the ADP file layout.

Common Use Cases and Queries

Typical use cases include building the ADP resident state tax extract, validating an employee's resident state withholding before a payroll run, and auditing exemptions or overrides across a business group. Because the view already flattens person, assignment, and tax rule data, it removes the need to replicate these joins in a custom extract.

A representative query filtering by employee number and business group:

• SELECT employee_number, gre_name, pay_frequency, fit_filing_status_code, fit_exemptions, fit_override_amount, medicare_exempt_flag FROM apps.hr_adp_resident_state_tax_v WHERE business_group_id = :p_bg_id AND employee_number = :p_emp_num;
• SELECT organization_id, assignment_number, primary_flag, fit_effective_start_date, fit_effective_end_date FROM apps.hr_adp_resident_state_tax_v WHERE person_id = :p_person_id ORDER BY fit_effective_start_date DESC;

Access to the view is mediated by HR_SECURITY, so results are automatically restricted to the operating user's security profile. The view should be treated as read-only reference data for integration purposes.