Results for “hr_adp_work_state_tax_v”

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

Overview

The HR_ADP_WORK_STATE_TAX_V view is an APPS-owned database object within the PER (Human Resources) product family of Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose in the ETRM dictionary is to serve as an ADP payroll interface view — that is, it presents the data required to export employee work-state tax and statutory filing information to the Automatic Data Processing (ADP) payroll service bureau. The view is registered as VALID in both release levels and is documented in the ETRM as a reporting and integration artifact rather than a transactional entity.

Because it is a view, it holds no data of its own. It renders a real-time projection over the base HR and Payroll tables, applying ADP-specific code translations (for example, mapping the number of fiscal periods per year to a single-character pay frequency, and mapping numeric filing status codes to alphanumeric equivalents) so that downstream ADP interfaces consume values in the format the service bureau expects. In practice, the view acts as the sanctioned extraction point for work-state withholding, SUI, SIT and related federal/state tax attributes for an assignment.

Underlying Base Objects

The ETRM metadata lists the referenced base objects and confirms the view is built exclusively on other APPS objects and synonyms. These fall into four functional groups:

• Employee and assignment data: PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_PERIODS_OF_SERVICE, PER_ADDRESSES, and the supporting packages HR_API, HR_GENERAL, HR_SECURITY, and HR_ADP.
• Organization and location data: HR_ALL_ORGANIZATION_UNITS, HR_LOCATIONS_ALL, HR_SOFT_CODING_KEYFLEX, and HR_LOOKUPS (a view used to resolve lookup meanings).
• US tax rules and reference data: PAY_US_EMP_FED_TAX_RULES_F, PAY_US_EMP_STATE_TAX_RULES_F, PAY_US_EMP_CITY_TAX_RULES_F, PAY_US_STATE_TAX_INFO_F, PAY_US_CITY_TAX_INFO_F, PAY_US_STATES, PAY_US_CITY_NAMES, PAY_US_COUNTIES, PAY_STATE_RULES, and PER_TIME_PERIOD_TYPES.
• Internal reference view: HR_ADP_EMP_REF_V, which supplies the COMPANY_CODE_EQUIVALENT value.

The synonym layer (DUAL, PER_*, PAY_*) is transparent at runtime; the view resolves to the underlying APPS tables and views.

Key Columns

• Identification: COMPANY_CODE_EQUIVALENT, GRE_NAME, GRE_ID, ORGANIZATION_ID, BUSINESS_GROUP_ID, PERSON_ID, EMPLOYEE_NUMBER, ASSIGNMENT_ID, and ASSIGNMENT_NUMBER.
• Assignment attributes: PRIMARY_FLAG and a translated ASSIGNMENT_SEQUENCE (primary assignments report sequence 0).
• Pay frequency: PAY_FREQUENCY, a DECODE on PTP.NUMBER_PER_FISCAL_YEAR producing W (52), S (24), B (26), or M (12).
• Federal tax (FIT) attributes: FIT_FILING_STATUS and its coded form FIT_FILING_STATUS_CODE, FIT_EXEMPTIONS (from WITHHOLDING_ALLOWANCES), FIT_OVERRIDE_AMOUNT, FIT_OVERRIDE_RATE, FIT_ADDITIONAL_TAX, EIC_FILING_STATUS, FIT_EXEMPT_FLAG, STATUTORY_EMPLOYEE_FLAG, and the Medicare, FUTA, SS, and cumulative-taxation exemption flags.
• State tax (SIT/SUI) attributes: SUI_STATE_NAME, SUI_STATE_CODE, SIT_EFFECTIVE_START_DATE, SIT_EFFECTIVE_END_DATE, SIT_EXEMPTIONS, SIT_OVERRIDE_AMOUNT, SIT_OVERRIDE_RATE, and SIT_FILING_STATUS (resolved via HR_LOOKUPS.MEANING).
• Effective-dating columns (FIT_EFFECTIVE_START_DATE/END_DATE) allow the consumer to establish which dated tax record applies to a given pay period.

Common Use Cases and Queries

The view is typically queried by ADP interface programs and by payroll analysts validating work-state setup before a transmission cycle.

• Extract active state tax records for a pay run — filter by assignment and current effective dates, using the PUSH_SUBQ hint to force late evaluation of the subquery on the tax rules fact tables.

SELECT employee_number, organization_id, sui_state_code,
       pay_frequency, sit_exemptions, sit_filing_status
FROM   apps.hr_adp_work_state_tax_v
WHERE  business_group_id = :p_bg_id
AND    sit_effective_start_date <= :p_effective_date
AND    NVL(sit_effective_end_date, :p_effective_date) >= :p_effective_date;

• Reconcile ADP exemptions against EBS — compare sit_exemptions and fit_exemptions to the values held in the PAY_US_EMP_STATE_TAX_RULES_F and PAY_US_EMP_FED_TAX_RULES_F rows to detect drift.
• Validate state assignment coverage — identify primary assignments (PRIMARY_FLAG = 'Y') lacking a valid SUI_STATE_CODE, which would prevent a compliant ADP submission.

Because access to PER_ and PAY_ data is governed by HR_SECURITY, queries executed under a restricted security profile return only the rows the session is authorized to see.