Search Results hr_legislation_subgroups




Overview
Explore in AI Chat

Overview

HR_LEGISLATION_SUBGROUPS is a Payroll (PAY) module reference table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines the subgroups that exist within a given legislation, where a legislation represents a country-level regulatory and payroll framework and a subgroup represents a finer administrative division within that framework. The documented example is that of individual states in the USA, where "US" is the legislation code and each state is a subgroup. The table therefore provides the geographic or jurisdictional hierarchy that payroll and HR processes use when a rule, tax treatment, or reporting requirement applies at a level below the national legislation.

The object carries a status of VALID and is classified in the ETRM metadata as standalone under the heuristic Data Vault classification mined from its foreign key structure. As a modeling suggestion, this classification indicates the table has no outbound foreign keys to other business entities and behaves as an independent reference set, rather than as a hub, link, or satellite. In Data Vault terms it is best treated as a small reference or lookup structure, populated and validated during implementation rather than derived from transactional activity.

Key Information Stored

The documented physical schema contains five columns. The most significant are:

  • LEGISLATION_CODE — the country or legislation identifier (for example, US) to which the subgroup belongs. This is part of the primary key and the principal join path to other legislation-scoped tables.
  • LEGISLATION_SUBGROUP — the subgroup code itself, such as a state abbreviation. This is the second component of the primary key and uniquely identifies a division within its legislation.
  • LEGISLATION_SUBGROUP_NAME — the descriptive, user-facing name of the subgroup (for example, the full state name), used in list of values and report output.
  • ACTIVE_INACTIVE_FLAG — indicates whether the subgroup is currently active. Inactive rows are retained for historical reference but are typically excluded from current selection lists and validation.
  • ZD_EDITION_NAME — the editioning column present in the 12.2.2 schema, supporting edition-based redefinition and online patching.

The primary key constraint HR_LEGISLATION_SUBGROUPS_PK is documented on the pair (LEGISLATION_CODE, LEGISLATION_SUBGROUP). The unique index HR_LEGISLATION_SUBGROUPS_PK is documented with three columns, (LEGISLATION_CODE, LEGISLATION_SUBGROUP, ZD_EDITION_NAME), which reflects the editioning-aware uniqueness model in 12.2.2. The business-key candidates for this table are therefore LEGISLATION_CODE plus LEGISLATION_SUBGROUP; there is no separate surrogate key column documented, so the composite business key serves as the principal identifier.

Common Use Cases and Queries

The table is most commonly used to populate lists of values in payroll and HR setup forms, to validate a subgroup entered against a legislation, and to drive jurisdiction-specific tax and reporting logic. A typical query retrieves all active subgroups for a legislation:

  • SELECT LEGISLATION_SUBGROUP, LEGISLATION_SUBGROUP_NAME FROM HR.HR_LEGISLATION_SUBGROUPS WHERE LEGISLATION_CODE = 'US' AND ACTIVE_INACTIVE_FLAG = 'Y' ORDER BY LEGISLATION_SUBGROUP_NAME;
  • Joining to a table such as PER_ADDRESSES: SELECT a.LEGISLATION_CODE, a.LEGISLATION_SUBGROUP, s.LEGISLATION_SUBGROUP_NAME FROM PER_ADDRESSES a, HR.HR_LEGISLATION_SUBGROUPS s WHERE s.LEGISLATION_CODE = a.LEGISLATION_CODE AND s.LEGISLATION_SUBGROUP = a.LEGISLATION_SUBGROUP;
  • Referential-integrity validation: SELECT COUNT(*) FROM HR.HR_LEGISLATION_SUBGROUPS WHERE LEGISLATION_CODE = :leg AND LEGISLATION_SUBGROUP = :sub AND ACTIVE_INACTIVE_FLAG = 'Y';

Reporting scenarios include producing a jurisdiction roster for tax filings, resolving a stored subgroup code to its display name, and auditing which subgroups are active versus retired. Because the table is small and reference-oriented, it is frequently cached in the middle tier and populated during localization setup.

Related Objects

Although the ETRM metadata classifies the table as standalone with no outbound foreign keys, it is referenced by legislation-scoped objects through the shared LEGISLATION_CODE and LEGISLATION_SUBGROUP columns:

  • HR_LEGAL_ENTITIES and other HR_LEGISLATION-based reference tables, joined on LEGISLATION_CODE.
  • PER_ADDRESSES, which stores a legislation and subgroup for each address, joined on LEGISLATION_CODE and LEGISLATION_SUBGROUP.
  • HR_LOCATIONS, where a location's jurisdiction may resolve to a subgroup.
  • PAY_US_STATE_RULES and similar country-specific payroll rule tables, which key off the subgroup.
  • HR_LOOKUPS, which supplies the underlying legislation and subgroup value sets.
  • PAY_LEGISLATION_DATA_GROUPS, which groups legislation-level payroll data elements and is logically associated through LEGISLATION_CODE.

Implementers should treat HR_LEGISLATION_SUBGROUPS as a shared reference table when designing extracts, ensuring that any process consuming subgroup values joins on both LEGISLATION_CODE and LEGISLATION_SUBGROUP to preserve uniqueness.