Search Results pay_us_garn_limit_rules_f




Overview
Explore in AI Chat

Overview

The PAY_US_GARN_LIMIT_RULES_F table is a payroll (PAY) module object owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the definition store for garnishment limit rules applied during US payroll processing. Each row describes the statutory or configurable ceiling that governs how much of an employee's disposable earnings may be withheld in response to a garnishment order, expressed as a maximum or minimum withholding amount and a maximum withholding duration in days.

The table is an effective-dated (date-tracked) entity, carrying EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns on both its primary and secondary unique constraints, together with the ZD_EDITION_NAME column used by the Oracle Data Vault (ZD) editioning model. This design allows the same logical rule to persist across multiple business periods while preserving a full historical audit trail.

The supplied metadata classifies this object heuristically as standalone under the Data Vault model, meaning no inbound or outbound foreign key relationships were mined from its constraint structure. As a modeling suggestion, this implies the table behaves as a self-contained reference or configuration satellite; in a formal Data Vault design it would most naturally be represented as a satellite attached to a garnishment rule hub keyed by LIMIT_RULE_ID.

Key Information Stored

The table contains fourteen documented columns. The most significant are listed below.

  • LIMIT_RULE_ID — Surrogate identifier for the garnishment limit rule; the first component of the primary key and the most reliable join key for downstream references.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date-tracked validity window of the rule. Both columns participate in the primary key and in the secondary unique key, enabling point-in-time resolution of which rule was in force for a given pay period.
  • GARN_CATEGORY — Classifies the garnishment category to which the rule applies (for example, the statutory category under which withholding limits are imposed).
  • STATE_CODE — Identifies the US state jurisdiction whose withholding limits the rule encodes. Because garnishment limits vary significantly by state, this is a primary driver of rule selection.
  • MAX_WITHHOLDING_AMOUNT — The ceiling on the amount that may be withheld per period.
  • MIN_WITHHOLDING_AMOUNT — The floor below which withholding must not fall.
  • MAX_WITHHOLDING_DURATION_DAYS — The maximum number of days over which withholding may continue under the rule.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle WHO audit columns recording the creating and last-updating user, login, and timestamps.
  • ZD_EDITION_NAME — Data Vault edition discriminator, appended to both unique indexes to support edition-based redefinition.

The primary key, PAY_US_GARN_LIMIT_RULES_F_PK, is defined on LIMIT_RULE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and ZD_EDITION_NAME — establishing the surrogate-plus-dates identity. The business-key candidate is captured by PAY_US_GARN_LIMIT_RULES_F_UK2 on STATE_CODE, GARN_CATEGORY, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and ZD_EDITION_NAME. This second index is the more meaningful uniqueness guarantee in functional terms: it ensures that, for any given state and garnishment category, no two overlapping effective-dated rules may coexist.

Common Use Cases and Queries

The principal use case is payroll garnishment calculation: during a payroll run, the payroll engine resolves, for each garnisheed employee, the applicable limit rule based on the employee's work state and the garnishment category, then applies MAX_WITHHOLDING_AMOUNT, MIN_WITHHOLDING_AMOUNT, and MAX_WITHHOLDING_DURATION_DAYS to cap or floor the deduction. A second use case is configuration maintenance, where administrators add or amend state-specific rules through the effective-dating model rather than updating rows in place.

A typical point-in-time lookup for a given state and category on a specific date takes the following form:

  • SELECT limit_rule_id, garn_category, state_code, max_withholding_amount, min_withholding_amount, max_withholding_duration_days FROM hr.pay_us_garn_limit_rules_f WHERE state_code = :p_state AND garn_category = :p_category AND :p_effective_date BETWEEN effective_start_date AND effective_end_date ORDER BY effective_start_date DESC;

Historical reporting queries commonly filter on EFFECTIVE_START_DATE ranges to reconstruct which limits applied during a prior tax year, and audit queries retrieve rows by CREATED_BY or LAST_UPDATED_BY for change control. Because the table is effective-dated, reporting logic should always constrain by date rather than selecting all rows for a state, to avoid double-counting superseded rule versions.

Related Objects

The mined relationship data classifies this table as standalone, so no formal foreign key relationships to parent objects are documented. In practice, the following objects interact with it functionally through the GARN_CATEGORY and STATE_CODE business keys and through the LIMIT_RULE_ID surrogate:

  • Garnishment rule and category definition objects in the PAY module, joined via GARN_CATEGORY, which supply the category semantics referenced by this table.
  • State and jurisdiction reference objects, joined via STATE_CODE, which validate the state jurisdiction used in rule resolution.
  • Payroll garnishment and deduction result tables produced by the payroll run, which consume the resolved MAX_WITHHOLDING_AMOUNT, MIN_WITHHOLDING_AMOUNT, and MAX_WITHHOLDING_DURATION_DAYS values as calculation inputs.
  • Standard Oracle WHO audit and date-tracking infrastructure, which governs the CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, and effective-dating columns.
  • Data Vault editioning infrastructure, which uses ZD_EDITION_NAME for edition-based redefinition of the constraints and columns.

Because the documented constraint data shows no referential dependencies, any integration or reporting layer should treat PAY_US_GARN_LIMIT_RULES_F as a reference source keyed on STATE_CODE and GARN_CATEGORY, resolved on an effective-dated basis.