Search Results psb_ws_lines_pos_u1

Overview

The PSB_WS_LINES_POSITIONS table is a core intersection table within the Oracle E-Business Suite Public Sector Budgeting (PSB) module, specifically for versions 12.1.1 and 12.2.2. It functions as a critical matrix or association table that manages the many-to-many relationship between budget worksheets (PSB_WORKSHEETS) and individual position budget lines (PSB_WS_POSITION_LINES). Its primary role is to enable the sharing of position line data across multiple worksheets, ensuring data is stored efficiently without duplication. This table is fundamental to the worksheet distribution and consolidation processes, tracking which position lines are assigned to specific worksheets for budgeting activities.

Key Information Stored

The table's structure is focused on the association and its attributes. The two mandatory foreign key columns form a composite primary key: WORKSHEET_ID and POSITION_LINE_ID. These columns uniquely identify the link between a specific worksheet and a specific position line. The table includes control flags that govern the behavior of the position line within the context of the associated worksheet: FREEZE_FLAG indicates if the position instance is frozen, preventing edits, and VIEW_LINE_FLAG controls its visibility. Standard Oracle "Who" columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) are present for auditing and tracking changes to each association record.

Common Use Cases and Queries

A primary use case is reporting and validating the distribution of position lines across global and distributed worksheets. Administrators or budget analysts often need to list all position lines attached to a specific worksheet or identify all worksheets containing a particular position line for troubleshooting or audit purposes. Common SQL patterns include joining to the related master tables to get descriptive information. For example, to find all position lines for a specific worksheet, including their status flags:

• SELECT wlp.*, pl.position_code, w.name as worksheet_name
• FROM psb.psb_ws_lines_positions wlp,
• psb.psb_ws_position_lines pl,
• psb.psb_worksheets w
• WHERE wlp.worksheet_id = :p_worksheet_id
• AND wlp.position_line_id = pl.position_line_id
• AND wlp.worksheet_id = w.worksheet_id;

Another critical query involves checking the freeze status of position lines within a worksheet to understand what data is locked from further modification during the budget cycle.

Related Objects

PSB_WS_LINES_POSITIONS sits at the center of a key relationship between two major PSB entities. It has defined foreign key relationships that enforce referential integrity:

• PSB_WORKSHEETS: The WORKSHEET_ID column references this master table of budget worksheets. This defines the "worksheet" side of the association.
• PSB_WS_POSITION_LINES: The POSITION_LINE_ID column references this table, which stores the detailed definition and amounts for individual position budget lines. This defines the "position line" side of the association.

The table's primary key constraint, PSB_WS_LINES1_PK, is defined on the combination of (WORKSHEET_ID, POSITION_LINE_ID), ensuring each association is unique. The unique index PSB_WS_LINES_POS_U1 enforces this constraint for performance.