Search Results pay_us_deduction_types_v




Overview
Explore in AI Chat

Overview

PAY_US_DEDUCTION_TYPES_V is an APPS-owned database view in the Oracle E-Business Suite Payroll (PAY) module, documented as VALID in ETRM for releases 12.1.1 and 12.2.2. The view presents a consolidated, language-aware catalog of United States deduction element types configured for a business group. It exposes deduction elements whose element classification falls into one of three US statutory reporting buckets: PRE-TAX DEDUCTIONS, VOLUNTARY DEDUCTIONS, and INVOLUNTARY DEDUCTIONS. Because it joins the element type definitions to their translated (TL) reporting names and their element classifications, the view functions as a reporting and integration surface rather than a transactional table. Typical consumers include custom reports, extracts, and interfaces that must enumerate available deduction plans for enrollment, payroll processing reconciliation, or benefits-to-payroll integration. The DISTINCT keyword in the view text guarantees that each qualifying element type is returned once, even where the underlying joins could otherwise fan out.

Underlying Base Objects

ETRM documents the view as being defined over four base objects, all accessed through APPS synonyms:

The view text joins PAY_ELEMENT_CLASSIFICATIONS to PAY_ELEMENT_CLASSIFICATIONS_TL on CLASSIFICATION_ID, joins PAY_ELEMENT_TYPES_F to PAY_ELEMENT_TYPES_F_TL on ELEMENT_TYPE_ID, and then links classifications to element types on CLASSIFICATION_ID. Both TL joins are restricted to LANGUAGE = USERENV('LANG'), so the view returns only the current session's language rows. Two negative filters exclude elements whose ELEMENT_NAME contains the substrings 'SPECIAL FEATURES' or 'SPECIAL INPUTS', suppressing helper or placeholder elements that are not genuine deduction plans.

Key Columns

  • ELEMENT_NAME — the translated name of the deduction element, drawn from PAY_ELEMENT_TYPES_F_TL.
  • ELEMENT_TYPE_ID — the primary identifier of the element type; the join key to payroll element definitions and to any downstream deduction or balance data.
  • BUSINESS_GROUP_ID — the business group that owns the element type, supporting multi-organization filtering.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range of the element type record.
  • LEGISLATION_CODE — the legislation under which the element is defined, typically 'US' for this view's intended population.
  • ELEMENT_INFORMATION10 — a descriptive flexfield-style information column carried from the element type definition.
  • PROCESSING_PRIORITY — the order in which the deduction element is processed during payroll runs.
  • REPORTING_NAME — the translated reporting name from the TL table.
  • REPORTING_NAME_ALT — derived as NVL(REPORTING_NAME, ELEMENT_NAME), guaranteeing a non-null display label.
  • CLASSIFICATION_NAME — the translated classification (PRE-TAX DEDUCTIONS, VOLUNTARY DEDUCTIONS, or INVOLUNTARY DEDUCTIONS).
  • CLASSIFICATION_ID — the identifier of the element classification.

Common Use Cases and Queries

The view is most often queried to list available US deduction plans for a business group, to populate LOVs in custom forms, or to reconcile deduction elements against classifications during payroll setup review.

SELECT element_name,
       reporting_name_alt,
       classification_name,
       processing_priority,
       effective_start_date,
       effective_end_date
FROM   apps.pay_us_deduction_types_v
WHERE  business_group_id = :p_business_group_id
ORDER  BY classification_name, processing_priority;

Because the view is date-effective, point-in-time queries should constrain or order by the effective dates:

SELECT element_type_id, element_name, classification_name
FROM   apps.pay_us_deduction_types_v
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY element_name;

A common integration pattern is to select ELEMENT_TYPE_ID and CLASSIFICATION_NAME as a lookup to drive benefit-to-payroll deduction mapping, while a reporting query groups by CLASSIFICATION_NAME to count plans per category. Queries should always account for the session language, since the TL joins restrict output to the current USERENV('LANG') value.