Search Results pay_run_type_usages_pk




Overview
Explore in AI Chat

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:

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.