Results for “psb_ws_fte_lines”

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

Overview

PSB_WS_FTE_LINES is a table within the PSB (Public Sector Budgeting) product of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2 releases. Its documented description is "Full-time equivalency by budget periods for positions in a worksheet." It therefore stores the period-by-period full-time equivalency (FTE) distribution that applies to a position line contained in a budgeting worksheet. Where PSB_WS_POSITION_LINES captures the position itself within the worksheet, PSB_WS_FTE_LINES captures how that position's FTE is allocated across each individual budget period, enabling detailed salary, labor, and headcount modeling during budget preparation.

From a Data Vault modeling perspective, the FK structure suggests a satellite-leaning classification: the table extends a parent entity (PSB_WS_POSITION_LINES) with descriptive and measure-like attributes (period FTE values), rather than acting as an independent hub or an association link. It is best understood as an attribute-bearing child of the position line.

Key Information Stored

The table is documented with 74 columns in the 12.1.1 physical schema. The primary key is maintained by PSB_WS_FTE_LINES_PK on the FTE_LINE_ID column, and a unique index PSB_WS_FTE_LINES_U1 also exists on FTE_LINE_ID, making it the surrogate identifier and business-key candidate. The most significant columns include:

• FTE_LINE_ID — surrogate primary key uniquely identifying each FTE line record.
• POSITION_LINE_ID — foreign key to PSB_WS_POSITION_LINES, tying the FTE record to its parent position line.
• SERVICE_PACKAGE_ID — foreign key to PSB_SERVICE_PACKAGES, associating the line with a service package.
• BUDGET_YEAR_ID — identifies the budget year the FTE line belongs to.
• ANNUAL_FTE — the annualized full-time equivalency for the position line.
• STAGE_SET_ID, START_STAGE_SEQ, CURRENT_STAGE_SEQ, END_STAGE_SEQ — workflow/staging identifiers tracking where the line sits within a budget stage sequence.
• PERIOD1_FTE through PERIOD60_FTE — the periodic FTE values, spanning up to sixty budget periods, providing the granular period-level distribution of effort.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO columns for audit and concurrency control.

Common Use Cases and Queries

Typical uses involve reporting total FTE per position or per service package across budget periods, validating that annual FTE reconciles with the sum of period FTE values, and extracting worksheet FTE data for integration or analysis. A representative query joins the FTE line to its parent position line and sums periodic values:

• Period reconciliation: compare ANNUAL_FTE against the sum of PERIOD1_FTE through PERIOD60_FTE for a given BUDGET_YEAR_ID.
• Position-level reporting: SELECT POSITION_LINE_ID, ANNUAL_FTE FROM PSB_WS_FTE_LINES WHERE BUDGET_YEAR_ID = :year.
• Service package analysis: aggregate FTE by SERVICE_PACKAGE_ID to evaluate staffing across service packages.
• Stage tracking: filter by CURRENT_STAGE_SEQ or STAGE_SET_ID to monitor lines at a particular approval stage.

Because period columns are wide (up to sixty), queries frequently use dynamic SQL or unpivot operations to convert period values into rows for analytical reporting.

Related Objects

The FK relationships documented for PSB_WS_FTE_LINES identify the primary dependent and referenced objects:

• PSB_WS_POSITION_LINES — parent table; joined on PSB_WS_FTE_LINES.POSITION_LINE_ID = PSB_WS_POSITION_LINES.POSITION_LINE_ID.
• PSB_SERVICE_PACKAGES — referenced table joined on SERVICE_PACKAGE_ID, providing service package context.
• PSB_WS_FTE_LINES_PK — primary key constraint enforcing uniqueness of FTE_LINE_ID.
• PSB_WS_FTE_LINES_U1 — unique index supporting the surrogate key.

Related PSB worksheet tables such as PSB_WS_HEADERS and PSB_BUDGET_YEARS provide broader worksheet and budget-year context through their parent relationships to the position line hierarchy.