Search Results pay_run_type_usages_uk1

Overview

PAY_RUN_TYPE_USAGES_F is a transactional payroll configuration table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines the parent-child hierarchy among run types where the parent run type is of the Cumulative type. In Oracle Payroll, a cumulative run type consolidates the results of subordinate (child) run types into a single processing unit, and this table stores those relationships. The "_F" suffix denotes a date-tracked (datetracked) table, meaning each row carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns that support Oracle's effective-dating framework. This allows historical and future-dated definitions of run type hierarchies to coexist and be queried as of a specific point in time.

Under a heuristic Data Vault classification mined from the foreign-key structure, this object is modeled as a standalone entity rather than a hub, link, or satellite. Practically, this reflects that the table is a self-contained configuration/association table whose business keys are not consolidated into a recognized hub pattern in the source taxonomy. It functions as a relationship container linking a parent run type to its child run types.

Key Information Stored

The table contains 15 documented columns. The most significant are:

• RUN_TYPE_USAGE_ID — Surrogate primary key uniquely identifying each usage row.
• PARENT_RUN_TYPE_ID — Foreign key reference to the cumulative (parent) run type.
• CHILD_RUN_TYPE_ID — Foreign key reference to the subordinate run type consolidated under the parent.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Datetrack columns defining the validity window of the relationship.
• SEQUENCE — Ordering of child run types within the parent processing sequence.
• BUSINESS_GROUP_ID — Multi-tenant key scoping the row to a specific business group.
• LEGISLATION_CODE — Identifies the legislative context for the run type hierarchy.
• OBJECT_VERSION_NUMBER — Optimistic locking control for concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.
• ZD_EDITION_NAME — Edition-based redefinition column supporting online patching (relevant in 12.2.x).

Two key constraints are documented. The primary key PAY_RUN_TYPE_USAGES_PK comprises RUN_TYPE_USAGE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE (with ZD_EDITION_NAME in the 12.2.2 physical definition). The unique business key PAY_RUN_TYPE_USAGES_UK1 spans PARENT_RUN_TYPE_ID, CHILD_RUN_TYPE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and BUSINESS_GROUP_ID, enforcing that a given parent-child pairing is unique within a business group for a given date range.

Common Use Cases and Queries

Typical scenarios include auditing run type hierarchies, reconciling cumulative payroll runs, and validating child sequencing. A common pattern retrieves currently active children for a parent:

• Join to PAY_RUN_TYPES_F on PARENT_RUN_TYPE_ID and CHILD_RUN_TYPE_ID to resolve run type names.
• Filter with SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE for point-in-time results.
• Order by SEQUENCE to reproduce the processing order.

Sample SQL:

SELECT parent.RUN_TYPE_NAME, child.RUN_TYPE_NAME, u.SEQUENCE
FROM PAY_RUN_TYPE_USAGES_F u, PAY_RUN_TYPES_F parent, PAY_RUN_TYPES_F child
WHERE u.PARENT_RUN_TYPE_ID = parent.RUN_TYPE_ID
AND u.CHILD_RUN_TYPE_ID = child.RUN_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN u.EFFECTIVE_START_DATE AND u.EFFECTIVE_END_DATE;

Reporting use cases include documenting payroll run configurations for audit, migrating run type setups between environments, and troubleshooting cumulative run results that omit expected child submissions.

Related Objects

• PAY_RUN_TYPES_F — The primary reference; joined via PARENT_RUN_TYPE_ID and CHILD_RUN_TYPE_ID.
• PAY_RUN_TYPE_USAGES_F (self) — Parent and child both resolve to run types defined here.
• PAY_RUN_TYPE_USAGES — Date-tracked view exposing the same rows for runtime access.
• PAY_RUN_RESULTS and PAY_RUN_RESULT_VALUES — Consume hierarchy definitions during cumulative processing.
• PAY_PAYROLL_ACTIONS — Invoke run types whose hierarchy is defined by these usages.
• HR_ALL_ORGANIZATION_UNITS / BUSINESS_GROUP_ID — Scope rows via the business group.
• PAY_RUN_TYPE_USAGES_PK / _UK1 — Constraint objects enforcing uniqueness.