Search Results pay_date_offset




Overview
Explore in AI Chat

Overview

PAY_ALL_PAYROLLS_F is the core payroll definition table in the Oracle E-Business Suite Payroll (PAY) module, owned by the HR schema. It stores the definition of every payroll group configured within a Business Group, including the rules, dates, costing accounts, and consolidation behavior that govern how payroll runs behave across Oracle EBS 12.1.1 and 12.2.2. Each row represents a dated (effective-dated) payroll record, distinguished by the surrogate primary key PAYROLL_ID and bounded by the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE business keys. Because payroll definitions are date-tracked, the table permits payroll attributes such as payment methods, offsets, or consolidation sets to change over time without losing history.

The heuristic Data Vault classification for this object is link, inferred from its FK structure. In practice, PAY_ALL_PAYROLLS_F functions as a transactional linkage between a Business Group, an organization, a consolidation set, and sets of keyflex accounting references. Modelers may treat it as a link table that connects the payroll aggregate to those related entities while retaining descriptive satellite attributes.

Key Information Stored

The table comprises 86 columns. The most significant for functional and reporting work are:

The primary key PAY_PAYROLLS_F_PK (PAYROLL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the only unique index documented, so it is also the business-key candidate. Descriptive columns such as ATTRIBUTE_CATEGORY and the ATTRIBUTE1–ATTRIBUTE20 and PRL_INFORMATION1–PRL_INFORMATION30 flexfields provide extensibility.

Common Use Cases and Queries

Typical uses include identifying all active payroll definitions for a Business Group, validating period generation setup, and driving payroll run reports. A representative query joins the payroll to its period type and consolidation set:

  • SELECT papf.payroll_id, papf.payroll_name, ptp.period_type_name, pcs.consolidation_set_name FROM pay_all_payrolls_f papf, per_time_period_types ptp, pay_consolidation_sets pcs WHERE papf.period_type = ptp.period_type AND papf.consolidation_set_id = pcs.consolidation_set_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
  • Effective-date filtering is essential because multiple rows per PAYROLL_ID exist; always constrain using SYSDATE between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
  • Costing audits join COST_ALLOCATION_KEYFLEX_ID and SUSPENSE_ACCOUNT_KEYFLEX_ID to PAY_COST_ALLOCATION_KEYFLEX to reconcile GL account derivation.
  • Period-generation and cutoff analysis leverages PERIOD_TYPE, FIRST_PERIOD_END_DATE, and the offset columns to anticipate pay dates.

Related Objects

The principal related objects are derived from the documented foreign keys:

  • HR_ALL_ORGANIZATION_UNITS – referenced via BUSINESS_GROUP_ID and ORGANIZATION_ID.
  • PAY_CONSOLIDATION_SETS – referenced via CONSOLIDATION_SET_ID.
  • PAY_COST_ALLOCATION_KEYFLEX – referenced twice via COST_ALLOCATION_KEYFLEX_ID and SUSPENSE_ACCOUNT_KEYFLEX_ID.
  • HR_SOFT_CODING_KEYFLEX – referenced via SOFT_CODING_KEYFLEX_ID.
  • PER_TIME_PERIOD_TYPES – referenced via PERIOD_TYPE.

Downstream objects such as PAY_PAYROLL_ACTIONS and payroll assignment records depend on this table to resolve which payroll an action or assignment belongs to. In Oracle EBS 12.2.2 the table retains the same effective-dated design introduced in 12.1.1, with the OBJECT_VERSION_NUMBER column supporting optimistic locking in the online forms.