Search Results pay_element_types_f_pk

Overview

PAY_ELEMENT_TYPES_F is the core definition table for payroll elements in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It resides in the HR schema and belongs to the PAY (Payroll) product. Within Oracle Payroll, an element represents any unit of compensation, deduction, or information that is processed during a payroll run — earnings, deductions, employer contributions, balance adjustments, and statutory items. PAY_ELEMENT_TYPES_F holds the header-level definition of each element, while its associated recurring and entry tables capture the values passed to it. As a datable ("_F" for "date-tracked") entity, element definitions are versioned by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, allowing historical preservation of element configuration changes over time.

The ETRM metadata classifies this object heuristically under a Data Vault model as a link. This is a reasonable modeling suggestion: the table sits between several independent reference entities — element classifications, benefit classifications, event groups, and time definitions — and its foreign keys connect them into a single business construct. A rigorous Data Vault design might alternatively split the element identity into a hub (the surrogate ELEMENT_TYPE_ID) with satellite records for the datable attributes.

Key Information Stored

The surrogate primary key is PAY_ELEMENT_TYPES_F_PK, defined on ELEMENT_TYPE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE (extended by ZD_EDITION_NAME in the 12.2.2 physical schema). A second unique index, PAY_ELEMENT_TYPES_F_UK2, serves as the principal business-key candidate, enforcing uniqueness on ELEMENT_NAME, BUSINESS_GROUP_ID, LEGISLATION_CODE, and the two effective dates. The most significant columns include:

• ELEMENT_TYPE_ID — surrogate identifier for the element definition, referenced broadly across payroll processing tables.
• ELEMENT_NAME — the user-facing name of the element, unique per business group and legislation.
• REPORTING_NAME — the name used on statutory and internal reports.
• BUSINESS_GROUP_ID and LEGISLATION_CODE — define the owning enterprise group and the legislative context, which govern which element templates and rules apply.
• CLASSIFICATION_ID — foreign key to PAY_ELEMENT_CLASSIFICATIONS, determining the element category (earnings, deduction, information, etc.).
• FORMULA_ID — the calculation formula attached to the element.
• PROCESSING_TYPE and PROCESSING_PRIORITY — control how and in what sequence the element is processed during a payroll run.
• MULTIPLE_ENTRIES_ALLOWED_FLAG, ADJUSTMENT_ONLY_FLAG, CLOSED_FOR_ENTRY_FLAG, and INDIRECT_ONLY_FLAG — behavioral flags governing entry and processing.
• POST_TERMINATION_RULE — defines processing behavior after an employee leaves.
• QUALIFYING_AGE, QUALIFYING_LENGTH_OF_SERVICE, and QUALIFYING_UNITS — eligibility criteria.
• BENEFIT_CLASSIFICATION_ID, PRORATION_GROUP_ID, and TIME_DEFINITION_ID — foreign keys tying the element to benefits, proration, and time-definition configuration.
• ATTRIBUTE_CATEGORY through ATTRIBUTE20 and ELEMENT_INFORMATION1 through ELEMENT_INFORMATION20 — descriptive flexfield and legislative information segments.
• Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) and OBJECT_VERSION_NUMBER for optimistic locking.

Common Use Cases and Queries

Typical usage centers on configuration reporting, element analysis, and integration with payroll processing. A frequent query lists all active elements for a business group and legislation:

• SELECT element_type_id, element_name, classification_id FROM pay_element_types_f WHERE business_group_id = :bg AND legislation_code = :leg AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Joining to PAY_ELEMENT_CLASSIFICATIONS to report elements by category: ... FROM pay_element_types_f pet, pay_element_classifications pec WHERE pet.classification_id = pec.classification_id AND ...
• Identifying elements attached to a specific formula: SELECT element_name FROM pay_element_types_f WHERE formula_id = :formula_id;
• Auditing configuration changes by comparing rows across EFFECTIVE_START_DATE versions for the same ELEMENT_TYPE_ID.
• Extracting element metadata into a data warehouse for analytics on pay structures, using ELEMENT_TYPE_ID as the durable key.

Related Objects

The foreign keys documented in the ETRM metadata establish the principal relationships:

• PAY_ELEMENT_CLASSIFICATIONS — joined via PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID = PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID.
• BEN_BENEFIT_CLASSIFICATIONS — joined via BENEFIT_CLASSIFICATION_ID.
• PAY_EVENT_GROUPS — joined via PRORATION_GROUP_ID.
• PAY_TIME_DEFINITIONS — joined via TIME_DEFINITION_ID.
• Child element tables such as PAY_ELEMENT_TYPES_TL (translations), PAY_ELEMENT_LINKS_F (element eligibility links), and PAY_ELEMENT_ENTRIES (entry values) depend on ELEMENT_TYPE_ID.
• Element input values are defined in PAY_INPUT_VALUES_F, the recurring feed in PAY_ELEMENT_ENTRY_VALUES_F, and balances in PAY_BALANCES, all conceptually tied through the element identity.

Together, these relationships make PAY_ELEMENT_TYPES_F the central configuration hub of the Oracle Payroll element model, linking classifications, formulas, event groups, and time definitions into a single processed pay construct.