Search Results psb_position_assignments_v




Overview
Explore in AI Chat

Overview

PSB_POSITION_ASSIGNMENTS_V is an Oracle E-Business Suite view owned by the APPS schema within the PSB (Public Sector Budgeting) product family. It exposes position assignment records used throughout the budgeting worksheet and position control flows in Oracle EBS 12.1.1 and 12.2.2. The view is a filtered projection over the PSB_POSITION_ASSIGNMENTS base table and is not a physical table; it delivers a curated subset of assignment data whose visibility is governed at runtime by the PSB_POSITIONS_PVT package.

The defining characteristic of this view is that its result set is context-sensitive. The WHERE clause references PSB_POSITIONS_PVT.GET_WORKSHEET_FLAG, GET_WORKSHEET_ID, and GET_END_DATE, which are PL/SQL package functions evaluated at query execution. As a result, the rows returned depend on the current budgeting worksheet context of the session, making the view suitable for worksheet-scoped reporting rather than a global snapshot of position assignment data.

Underlying Base Objects

The view is defined over a single documented base object:

  • PSB_POSITION_ASSIGNMENTS (aliased PAS) — the primary source of all columns exposed by the view.

Candidate rows are restricted by three branches of a Boolean condition. The first branch returns rows whose WORKSHEET_ID matches the worksheet identifier returned by PSB_POSITIONS_PVT.GET_WORKSHEET_ID. The second and third branches handle rows where WORKSHEET_ID is null, treating ATTRIBUTE and ELEMENT assignment types separately. Both branches use a correlated NOT EXISTS subquery that self-references PSB_POSITION_ASSIGNMENTS (aliased C) and applies date-overlap logic to suppress duplicate or shadowed records across effective date ranges. This self-referential anti-join is the mechanism that prevents overlapping assignment definitions from appearing simultaneously.

No other base tables are documented as referenced objects, although the view logically depends on the PSB_POSITIONS_PVT package for its filter predicates.

Key Columns

The view projects the ROWID of the base row along with the full assignment column set. Significant columns include:

Common Use Cases and Queries

The view is principally used to retrieve assignment data within an active budgeting worksheet, for validation of position assignments, and for extraction routines identified via DATA_EXTRACT_ID. Typical usage includes confirming which attributes and elements are attached to a position, and verifying defaulting behavior through the global default and default rule flags.

A basic query retrieves assignments for the current worksheet context:

  • SELECT position_assignment_id, position_id, assignment_type, attribute_value, element_value FROM apps.psb_position_assignments_v;
  • SELECT pa.position_id, pa.assignment_type, pa.effective_start_date, pa.effective_end_date FROM apps.psb_position_assignments_v pa WHERE pa.worksheet_id IS NOT NULL ORDER BY pa.position_id;

Because row visibility depends on PSB_POSITIONS_PVT session state, direct ad hoc queries outside a properly initialized budgeting session may return few or no rows. Reports and concurrent programs that reference this view should be executed within the intended worksheet context to obtain meaningful results.