Search Results per_assignment_budget_values




Overview
Explore in AI Chat

Overview

PER_ASSIGNMENT_BUDGET_VALUES is a date-effective view owned by the APPS schema in Oracle E-Business Suite, belonging to the PER (Human Resources) product family. It presents assignment-level budget value records — the monetary or unit amounts associated with an employee assignment for budgeting purposes — as they are valid on the current effective date of the user's session. The view was documented in ETRM for both 12.1.1 and 12.2.2 with a status of VALID and is categorized as a date effective view.

Its principal role is to shield reporting and integration consumers from the multi-row history stored in the underlying table. Rather than requiring callers to supply their own effective-date predicates, the view filters rows against the session effective date maintained in FND_SESSIONS, so a query naturally returns only the currently effective budget value for each assignment. This makes it suitable for concurrent programs, OBIEE/Discoverer reports, and interfaces that must reflect the business group's "as of" date rather than the physical system date.

Underlying Base Objects

The view is defined over a single base object, PER_ASSIGNMENT_BUDGET_VALUES_F, which is a synonym resolving to the date-effective (_F) table in the APPS schema. No joins are performed against other business tables; the only additional reference is to FND_SESSIONS, the Applications session table, which is used solely to supply the effective date for the WHERE clause.

The defining predicate restricts rows to those where EFFECTIVE_START_DATE is on or before the session effective date and EFFECTIVE_END_DATE is on or after it:

  • EFFECTIVE_START_DATE <= (SELECT EFFECTIVE_DATE FROM FND_SESSIONS WHERE FND_SESSIONS.SESSION_ID = USERENV('SESSIONID'))
  • EFFECTIVE_END_DATE >= (SELECT EFFECTIVE_DATE FROM FND_SESSIONS WHERE FND_SESSIONS.SESSION_ID = USERENV('SESSIONID'))

Because FND_SESSIONS is keyed by SESSION_ID from USERENV, the effective date is derived from the Oracle Forms/applications session context. When the view is queried outside a properly initialized applications session — for example through a raw SQL*Plus connection — the subquery may return no rows and the view yields no data. Callers should therefore ensure FND_SESSION_MANAGEMENT or a standard applications login has established the session before relying on the results.

Key Columns

The view exposes the complete set of date-effective columns from the base table:

Common Use Cases and Queries

Typical uses include reporting assignment-level budget allocations for the current effective date, feeding payroll or costing interfaces, and reconciling budgeted versus actual assignment values by business group. Because the view is already date-filtered, queries are comparatively simple.

Retrieve all currently effective budget values for a business group:

SELECT abv.assignment_id,
       abv.unit,
       abv.value,
       abv.effective_start_date,
       abv.effective_end_date
FROM   apps.per_assignment_budget_values abv
WHERE  abv.business_group_id = :p_business_group_id
ORDER  BY abv.assignment_id;

Join to the assignment to obtain person context:

SELECT pa.assignment_id,
       pa.person_id,
       abv.unit,
       abv.value
FROM   apps.per_assignment_budget_values abv,
       apps.per_assignments_f pa
WHERE  pa.assignment_id = abv.assignment_id
AND    TRUNC(SYSDATE) BETWEEN pa.effective_start_date AND pa.effective_end_date
AND    pa.business_group_id = abv.business_group_id;

Aggregate total budget by unit:

SELECT abv.unit, SUM(abv.value) total_value
FROM   apps.per_assignment_budget_values abv
GROUP  BY abv.unit;

When historical analysis is required, query the base table PER_ASSIGNMENT_BUDGET_VALUES_F directly with explicit date predicates, since the view returns only the session-effective row. Developers should prefix references with the APPS schema or create a synonym as appropriate to their environment.