Search Results pay_cost_allocations_d




Overview
Explore in AI Chat

Overview

PAY_COST_ALLOCATIONS_D is a DateTrack history view owned by the APPS schema in Oracle E-Business Suite (validated for 12.1.1 and 12.2.2). It belongs to the PAY (Payroll) product family and exists specifically to support DateTrack History functionality. In Oracle Payroll, cost allocations define how an employee's payroll costs are distributed across General Ledger accounts, typically expressed as a percentage split across one or more accounting flexfield combinations. DateTrack allows users to make date-effective changes to such records, retaining prior versions rather than overwriting them.

The "_D" suffix denotes a date-tracked (history) view. Its principal role is to expose the full historical progression of cost allocation records — every dated version of an allocation rather than only the currently effective row — so that users and concurrent programs can display or audit the "History" of allocations from the Cost Allocations form. It is a read-only reporting and inquiry construct and should not be treated as a base DML table. In reporting and integration contexts, it provides a denormalized, human-readable projection of allocation data, including the resolved concatenated accounting flexfield segments and the name of the user who last updated the record, making it convenient for audit reports and extracts.

Underlying Base Objects

The view is defined over three documented base objects:

Because "_F" tables carry DateTrack columns, the "_D" view simply surfaces all effective-dated versions. The ROUND((PCOST.PROPORTION * 100), 2) expression converts the stored proportion into a two-decimal percentage; this expression is aliased in the column list as PROPORTION, while CKEY.CONCATENATED_SEGMENTS is exposed as COST_CODE.

Key Columns

The documented columns are:

  • COST_ALLOCATION_ID — surrogate identifier of the allocation record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack validity window of each historical version.
  • PROPORTION — the allocation percentage, derived as PROPORTION × 100 rounded to two decimals.
  • COST_CODE — the concatenated accounting flexfield segments for the allocation, sourced from PAY_COST_ALLOCATION_KEYFLEX. This is the value most commonly matched when a user searches on "cost_code."
  • LAST_UPDATE_DATE — timestamp of the most recent change to the row.
  • LAST_UPDATED_BY — the FND_USER.USER_ID of the user who last updated the record.

Although LEADING_COLUMN_LIST members LAST_UPDATED_BY and USER_NAME are not both enumerated in the excerpt above, the view text resolves LAST_UPDATED_BY against FND_USER to render the updater's USER_NAME, enriching the audit context of each dated version.

Common Use Cases and Queries

Typical uses include auditing allocation history, reconciling payroll cost distribution changes over time, and reporting which accounting combination (cost code) an employee's costs were allocated to on a given date.

  • Display all cost codes for a given allocation:
SELECT cost_allocation_id, effective_start_date, effective_end_date,
       proportion, cost_code, last_update_date
FROM   apps.pay_cost_allocations_d
WHERE  cost_code LIKE :p_cost_code
ORDER BY cost_allocation_id, effective_start_date DESC;
  • As-at-date cost code lookup: filter EFFECTIVE_START_DATE <= :as_of AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE >= :as_of).
  • Audit trail: join to FND_USER via LAST_UPDATED_BY to attribute changes, complementing the USER_NAME column already exposed.

Because the view performs the keyflex and user joins and the percentage rounding automatically, it is preferable to querying the base "_F" table directly when cost codes and updater names are required.