Search Results pay_iterative_rules_f




Overview
Explore in AI Chat

Overview

PAY_ITERATIVE_RULES_F is a core configuration table in the Oracle E-Business Suite Payroll (PAY) module, owned by the HR schema. Its documented purpose is to hold the processing rules of iterative elements. Iterative elements are payroll elements that execute repeatedly within a payroll run until a defined condition or convergence criterion is satisfied — a pattern used for retroactive processing, cumulative recalculations, and interdependent element computations. This table stores the metadata that governs those repeated evaluations: which result value is tested, what type of iteration rule applies, and which input value or element type the rule is bound to.

The table is date-tracked, using EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns, which makes it a datable (non-key-flexfield) entity rather than a key flexfield structure. In ETRM 12.2.2 the physical schema documents 17 columns. The heuristic Data Vault classification mined from the foreign-key structure is standalone; in modeling terms this suggests treating PAY_ITERATIVE_RULES_F as a hub-like entity anchored on ITERATIVE_RULE_ID, with its descriptive attributes and effective-dating best represented as a satellite. Because the FK mining shows no hard parent-child dependency, the table should be treated as a reference/configuration source rather than a transactional link.

Key Information Stored

The surrogate primary key is defined by PAY_ITERATIVE_RULES_F_PK over ITERATIVE_RULE_ID together with the effective-date range (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) and ZD_EDITION_NAME. A second unique index, PAY_ITERATIVE_RULES_UK1, serves as the business-key candidate, enforcing uniqueness across RESULT_NAME, ITERATIVE_RULE_TYPE, INPUT_VALUE_ID, ELEMENT_TYPE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and ZD_EDITION_NAME.

  • ITERATIVE_RULE_ID — surrogate identifier for each iteration rule; the anchor of the primary key.
  • ELEMENT_TYPE_ID — the payroll element type to which the iterative rule applies.
  • RESULT_NAME — the result value name whose outcome drives or is produced by iteration.
  • ITERATIVE_RULE_TYPE — classification of the iteration behavior (for example, the rule category governing convergence or repetition).
  • INPUT_VALUE_ID — the input value that the rule reads or writes during each pass.
  • SEVERITY_LEVEL — severity assigned to the rule outcome, used for messaging or error handling.
  • LEGISLATION_CODE — the legislative context under which the rule is valid.
  • BUSINESS_GROUP_ID — the business group (enterprise) that owns the configuration row.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective validity window; also part of both keys.
  • OBJECT_VERSION_NUMBER — optimistic locking version for concurrent updates.
  • ZD_EDITION_NAME — editioning column introduced in the 12.2 online-patching architecture.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical scenarios include reviewing why an iterative element terminates after a given number of passes, auditing which rules apply to a specific element type, and reporting on configuration differences across legislation or business group. Because the table is date-effective, queries should constrain the effective window to the date of interest.

  • Current rules for a given element type: SELECT * FROM pay_iterative_rules_f WHERE element_type_id = :p_element_type AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Resolving a result name through the business key: SELECT iterative_rule_id, iterative_rule_type FROM pay_iterative_rules_f WHERE result_name = :p_result AND effective_end_date = HR_GENERAL.G_MAX_END_DATE;
  • Detecting overlapping or duplicate effective-dated configuration by grouping on RESULT_NAME and ITERATIVE_RULE_TYPE.
  • Feeding payroll diagnostics: list all rules with SEVERITY_LEVEL above a threshold for a legislation code.

Reporting joins typically resolve ELEMENT_TYPE_ID and INPUT_VALUE_ID to their respective definition tables to present human-readable names alongside the technical rule identifiers.

Related Objects

Because the heuristic classification is standalone, most relationships are soft or value-based rather than enforced foreign keys, and joins should always be constrained by effective dates.