Results for “position_segments”

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

Overview

PSB_SELECT_POSITIONS_V is a reporting view within the Public Sector Budgeting (PSB) module of Oracle E-Business Suite. Public Sector Budgeting was a legacy budgeting application used by public sector and government organizations to model position and salary budgets before being superseded by newer budgeting functionality. The module carries the "Obsolete" designation in Oracle EBS 12.1.1 and 12.2.2, meaning Oracle no longer supports or patches it, though the underlying schema objects may still be present in upgraded environments that historically used PSB.

The view serves as a denormalized presentation layer over position data. Rather than presenting raw staging rows, it resolves descriptive attributes — job name, employee identifier, employee number, and employee name — at query time by invoking PL/SQL functions in the PSB_POSITIONS_PVT package. This design allows position records for a given budget data extract to be reported alongside the human-readable identity of the incumbent assigned to each position. For users searching on "employee_name," this view is the primary PSB object that exposes an EMPLOYEE_NAME column, and it is the most practical entry point for resolving an employee's name from a position or budget extract record.

Underlying Base Objects

The documented view definition selects exclusively from a single base table: PSB_POSITIONS. No additional base tables are documented in the ETRM metadata beyond this parent object. The view does not perform conventional joins; instead, it projects columns from PSB_POSITIONS and augments them with computed values returned by three package functions:

• PSB_POSITIONS_PVT.GET_JOB_NAME — resolves the job name associated with the position.
• PSB_POSITIONS_PVT.GET_EMPLOYEE_ID — resolves the internal employee identifier.
• PSB_POSITIONS_PVT.GET_EMPLOYEE_NUMBER — resolves the employee number.
• PSB_POSITIONS_PVT.GET_EMPLOYEE_NAME — resolves the employee name.

Each function is passed three arguments: the DATA_EXTRACT_ID from the position row, a NULL second argument, and the POSITION_ID. The DATA_EXTRACT_ID correlates the position to a specific budgeting extract run, and the position identifier selects the row within that extract. Because resolution occurs through PL/SQL rather than SQL joins, the view's performance and output depend on the state of the PSB staging data for the referenced extract.

Key Columns

The view exposes the following columns:

• POSITION_ID — Primary identifier of the position record.
• POSITION_NAME — Name of the position. Note that the view text selects POS.NAME twice, so the column list includes the position name in two positions; the documented column set labels the second occurrence POSITION_NAME and includes POSITION_SEGMENTS in the metadata's column listing.
• ORGANIZATION_ID — Organization owning the position.
• POSITION_DEFINITION_ID — Reference to the position definition.
• NEW_POSITION_FLAG — Indicates whether the position is newly created; defaulted to 'N' via NVL.
• POSITION_SEGMENTS — Concatenated position flexfield segments.
• JOB_NAME — Job name resolved through PSB_POSITIONS_PVT.
• EMPLOYEE_ID — Employee identifier resolved through PSB_POSITIONS_PVT.
• EMPLOYEE_NUMBER — Employee number resolved through PSB_POSITIONS_PVT.
• EMPLOYEE_NAME — Employee name resolved through PSB_POSITIONS_PVT; this is the column most frequently targeted by searches.
• DATA_EXTRACT_ID — Identifier of the budget data extract to which the position belongs.

Common Use Cases and Queries

Typical usage centers on reviewing or reconciling position and incumbent information for a budgeting extract, particularly where the requesting user knows a position but not the occupant, or vice versa.

To list all positions within a specific extract along with incumbent details:

• SELECT position_id, position_name, job_name, employee_number, employee_name FROM psb_select_positions_v WHERE data_extract_id = :extract_id;

To locate a position by employee name:

• SELECT position_id, position_name, organization_id, employee_name FROM psb_select_positions_v WHERE UPPER(employee_name) LIKE UPPER('%'||:name||'%');

To identify newly created positions in an extract:

• SELECT position_id, position_name, new_position_flag FROM psb_select_positions_v WHERE data_extract_id = :extract_id AND new_position_flag = 'Y';

Because EMPLOYEE_NAME is produced by a PL/SQL function call, filtering on it prevents index use and requires a full scan of PSB_POSITIONS rows for the extract; restricting by DATA_EXTRACT_ID first is advisable. Given that PSB is obsolete, the view may not be deployed in all environments, and the ETRM metadata notes "Not implemented in this database" for the documented instance, so availability should be verified before reliance.