Search Results pay_all_deduction_types_v




Overview
Explore in AI Chat

Overview

PAY_ALL_DEDUCTION_TYPES_V is an APPS-owned database view in the Oracle E-Business Suite Payroll (PAY) module. It exposes a denormalized, reporting-friendly representation of deduction element types configured in a payroll business group. Rather than requiring report developers and integration specialists to join several core payroll tables directly, the view consolidates element type definition rows, their translated names, classifications, benefit classifications, and a series of element information attributes into a single queryable object.

Because the view is defined with the ALL prefix, it is intended to span business groups: the business group identifier is exposed as a selectable column, whereas many core element type tables carry the business group as part of their key structure. The view is VALID and present in both Oracle EBS 12.1.1 and 12.2.2, and is commonly used as the population set for deduction-related validation, element association processing, and configuration reporting. It is also referenced internally by payroll code paths that resolve associated elements (employer and assignment-level associations), making it a functional dependency as well as a reporting convenience.

Underlying Base Objects

The documented ETRM metadata lists the following referenced base objects for the 12.2.2 definition: BEN_BENEFIT_CLASSIFICATIONS (synonym), FND_SESSIONS (synonym), HR_GENERAL (package), HR_USER_INIT_DEDN (package), PAY_ELEMENT_CLASSIFICATIONS (synonym), PAY_ELEMENT_CLASSIFICATIONS_TL (synonym), PAY_ELEMENT_TYPES_F (synonym), and PAY_ELEMENT_TYPES_F_TL (synonym).

  • PAY_ELEMENT_TYPES_F is the driving table, holding effective-dated element type definitions keyed by ELEMENT_TYPE_ID plus effective dates. The view exposes the element information attribute columns (ELEMENT_INFORMATION1 through ELEMENT_INFORMATION20) that carry deduction-specific configuration.
  • PAY_ELEMENT_TYPES_F_TL supplies the translated element name, reporting name, and description.
  • PAY_ELEMENT_CLASSIFICATIONS (and its _TL translation) provides the classification, which determines whether a given element is a deduction and what category of deduction it represents.
  • BEN_BENEFIT_CLASSIFICATIONS adds the benefit classification name used when the deduction is linked to a benefits plan.
  • HR_GENERAL is invoked to decode lookups, and HR_USER_INIT_DEDN is invoked to resolve associated element identifiers for employer and assignment associations.
  • FND_SESSIONS appears as a referenced object, typically in the context of session-dependent decode logic.

Key Columns

Common Use Cases and Queries

Typical uses include listing all active deductions for a business group, identifying deductions by category, and driving element association checks against the balance type columns.

  • Retrieve deduction types for a business group as of a date:
    SELECT element_name, classification_name, category FROM pay_all_deduction_types_v WHERE business_group_id = :p_bg_id AND :p_date BETWEEN effective_start_date AND effective_end_date;
  • List voluntary and involuntary deductions:
    SELECT element_name, category FROM pay_all_deduction_types_v WHERE classification_name IN ('Voluntary Deductions','Involuntary Deductions');

Because the view performs lookups through HR_GENERAL and HR_USER_INIT_DEDN, queries can be comparatively expensive on large business groups; filtering on BUSINESS_GROUP_ID and effective dates is recommended to keep results bounded.