Results for “per_budget_elements_v”

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

Overview

PER_BUDGET_ELEMENTS_V is a read-only view owned by the APPS schema in Oracle E-Business Suite (validated in 12.1.1 and 12.2.2) within the PER — Human Resources product family. Its documented purpose is to support the user interface. Functionally, it denormalizes budget lines stored in PER_BUDGET_ELEMENTS by resolving the surrogate identifiers of the organization, job, position, and grade into their current, language-appropriate display names and effective dates. This makes the view suitable for concurrent-program extracts, ad-hoc reporting, and OAF-based pages that must present a budget element together with readable context rather than raw IDs.

Underlying Base Objects

The view text joins PER_BUDGET_ELEMENTS (BEL) to ten documented base objects. Effective-dated and translated names are supplied by PER_JOBS / PER_JOBS_TL, PER_GRADES / PER_GRADES_TL, HR_ALL_ORGANIZATION_UNITS / HR_ALL_ORGANIZATION_UNITS_TL, and HR_ALL_POSITIONS / HR_ALL_POSITIONS_F_TL. All joins are outer joins, so budget elements without a matching grade, position, or job still appear. Translation joins are constrained by USERENV('LANG'), returning the session-language row. Two PL/SQL functions encapsulated in the HR_GENERAL package — DECODE_POSITION_LATEST_NAME and GET_POSITION_DATE_END — derive the position name and position end date, since positions are maintained through date-tracked entities rather than simple columns. This dependency means HR_GENERAL must be valid and compiled in the database for the view to function.

Key Columns

• ROW_ID — the ROWID of the underlying PER_BUDGET_ELEMENTS row, used for locking or direct row access.
• BUDGET_ELEMENT_ID — the primary key of the budget element line.
• BUSINESS_GROUP_ID, GRADE_ID, JOB_ID, POSITION_ID, ORGANIZATION_ID — surrogate foreign keys retained from the base table; BUSINESS_GROUP_ID is offset by +0 to defeat synonym caching.
• BUDGET_VERSION_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — budget version and concurrent-program audit context.
• ORGANIZATION_NAME, JOB_NAME, POSITION_NAME, GRADE_NAME — resolved display names for the respective entities.
• JOB_START_DATE / JOB_END_DATE — derived from PER_JOBS.DATE_FROM and DATE_TO. These columns are the target of the user's search term "job_start_date" and were added precisely to expose the job's effective range alongside each budget element.
• POS_START_DATE / POS_END_DATE — position effective dates, the end date resolved via HR_GENERAL.GET_POSITION_DATE_END.
• GRADE_START_DATE / GRADE_END_DATE — grade effective dates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard WHO audit columns.

Common Use Cases and Queries

Typical uses include budget-versus-actual comparisons, listing budgeted jobs or grades for a budget version, and filtering budget lines by the effective window of the underlying job. Because the search term was "job_start_date," the most common query pattern filters on the job effective window:

• List all elements for a budget version with job and organization names.
• Find budget lines whose job started on or after a given date.
• Detect budget elements whose job has ended (inactive or obsolete jobs).
• Extract budgeted positions per organization for headcount planning.

Sample SQL:

SELECT budget_element_id, organization_name, job_name, job_start_date, job_end_date, grade_name
FROM apps.per_budget_elements_v
WHERE business_group_id = :p_bg_id
  AND budget_version_id = :p_version_id
  AND job_start_date >= TO_DATE(:p_from,'YYYY-MM-DD')
ORDER BY organization_name, job_name;

To locate budget lines tied to jobs that have ended:

SELECT budget_element_id, job_name, job_start_date, job_end_date
FROM apps.per_budget_elements_v
WHERE job_end_date IS NOT NULL AND job_end_date < SYSDATE;

Because the view performs several outer joins and calls two package functions, queries benefit from predicates on BUDGET_VERSION_ID or BUSINESS_GROUP_ID, and from indexing on PER_BUDGET_ELEMENTS. Callers should grant SELECT on APPS.PER_BUDGET_ELEMENTS_V and must have EXECUTE on HR_GENERAL and access to the HR translation views, or the query will fail despite the synonym being valid.