Results for “per_assignment_budget_valu_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PER_ASSIGNMENT_BUDGET_VALUES_F is the HR schema table that stores budgeted values associated with an assignment in Oracle EBS 12.1.1 and 12.2.2. The table is DateTracked, meaning each row carries an EFFECTIVE_START_DATE and EFFECTIVE_END_DATE that delimit the period in which the budget value is valid. Assignments may be budgeted in headcount, FTE (full time equivalent), points, or any user-defined unit, and Oracle HRMS aggregates the values held here when calculating actual headcount figures for employee assignments.

Applicant assignment budget values are not consumed directly by headcount calculations, but they are retained so that when an applicant is subsequently hired into the assignment, the pre-existing budget values are automatically carried into future headcount calculations. If a default budget value is defined at the business group level for a given budget type, that default is created for each new assignment.

From a Data Vault modeling perspective, the FK structure and DateTracked effective-dating suggest this object behaves as a satellite-leaning entity. Its natural key (ASSIGNMENT_ID plus effective dates plus UNIT) attaches descriptive budget attributes to the assignment hub, with the surrogate ASSIGNMENT_BUDGET_VALUE_ID serving as a non-business identifier.

Key Information Stored

The table is documented with 17 columns, of which the following are most significant:

• ASSIGNMENT_BUDGET_VALUE_ID — system-generated surrogate primary key (NUMBER(15)).
• ASSIGNMENT_ID — the assignment to which the budget value applies; part of the unique business key.
• UNIT — the budget unit (headcount, FTE, points, or user-defined); part of the unique business key.
• VALUE — the numeric budget value for the assignment, unit, and effective period.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — DateTrack effective-dating bounds; part of the primary key and both unique indexes.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the record to a business group.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/ADF framework.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the process that last wrote the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard "WHO" audit columns.

Two unique indexes exist: PER_ASSIGNMENT_BUDGET_VALU_PK on ASSIGNMENT_BUDGET_VALUE_ID with effective dates (the surrogate key), and PER_ASSIGNMENT_BUDGET_VALU_UK2 on ASSIGNMENT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and UNIT (the true business-key candidate). Both reside in the APPS_TS_TX_IDX tablespace; data is stored in APPS_TS_TX_DATA with PCT FREE 10.

Common Use Cases and Queries

Typical reporting scenarios compare budgeted versus actual headcount or FTE per assignment, organization, or business group. Because the table is DateTracked, queries must constrain the effective dates to obtain the value in force on a given date.

A common pattern joins budget values to assignments and organizations:

• Current budget for an assignment: SELECT unit, value FROM per_assignment_budget_values_f WHERE assignment_id = :p_assignment_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Budget by unit for a business group at a point in time, aggregating VALUE by UNIT and joining HR_ALL_ORGANIZATION_UNITS on BUSINESS_GROUP_ID.
• Headcount variance reporting that compares summed FTE budget values to actual assignment FTE figures.
• Audit queries leveraging REQUEST_ID and PROGRAM_ID to trace which concurrent process populated or updated budget values.

Because the UK enforces one row per assignment, unit, and effective period, BI Publisher and OTBI-style extracts should use the effective-dated predicates to avoid duplicate-row inflation.

Related Objects

• HR.HR_ALL_ORGANIZATION_UNITS — referenced by BUSINESS_GROUP_ID; the parent business group/organization for each budget row.
• HR.PER_ALL_ASSIGNMENTS_F — the DateTracked assignment entity keyed by ASSIGNMENT_ID; the primary parent of budget values.
• HR.PER_ASSIGNMENTS_F — assignment operational view commonly joined for assignment-level reporting.
• PER_ASSIGNMENT_BUDGET_VALUES — the DateTracked base/distribution object underlying this "_F" (frozen/effective) table convention.
• HR.PER_BUSINESS_GROUPS — defines business group defaults for budget types.
• HR.PER_BUDGET_VALUES_KEY and related budget value key flexfield structures — define valid UNIT values.
• APIs: the Assignment API (HR_ASSIGNMENT_API) and DateTrack framework routines that insert/update budget values when assignments are created or hired.
• Concurrent programs: budget value defaulting and headcount calculation processes, identifiable via PROGRAM_ID/REQUEST_ID.