Results for “psb_element_lines_pk”

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

Overview

PSB_WS_ELEMENT_LINES is a transactional table in the PSB – Public Sector Budgeting module of Oracle E-Business Suite (validated in 12.1.1 and 12.2.2). It stores the cost breakdown of total position cost by pay elements for a budget worksheet, meaning each row decomposes the cost of a specific position line into its contributing salary and benefit components (pay elements). This decomposition supports detailed compensation analysis, salary and benefit modeling, and what-if scenario planning during the public sector budgeting cycle.

From a heuristic Data Vault modeling perspective, the table is classified as a link, since its foreign key structure associates position lines with pay elements and other budgeting dimensions (element sets and service packages). It functions as an intersection-style detail table rather than a standalone hub entity.

Key Information Stored

The surrogate primary key is ELEMENT_LINE_ID, enforced by the PSB_ELEMENT_LINES_PK constraint. A unique index, PSB_WS_ELEMENT_LINES_U1, also exists on ELEMENT_LINE_ID, confirming it as the sole documented business-key candidate. The most significant columns include:

• POSITION_LINE_ID – references PSB_WS_POSITION_LINES; identifies the parent position line whose cost is being broken down.
• PAY_ELEMENT_ID – references PSB_PAY_ELEMENTS; identifies the pay element contributing to cost (e.g., base salary, benefits, allowances).
• BUDGET_YEAR_ID – the budget year to which the element cost applies.
• CURRENCY_CODE – currency in which ELEMENT_COST is expressed.
• ELEMENT_COST – the monetary value of the pay element for this position line.
• ELEMENT_SET_ID – references PAY_ELEMENT_SETS, grouping the elements applied.
• SERVICE_PACKAGE_ID – references PSB_SERVICE_PACKAGES; links to the applicable service package.
• STAGE_SET_ID, START_STAGE_SEQ, CURRENT_STAGE_SEQ, END_STAGE_SEQ – stage-tracking columns supporting the worksheet's staged budgeting workflow.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE – standard who-audit columns.
• FUNCTIONAL_TRANSACTION – associates the row with a functional transaction context.

Common Use Cases and Queries

Typical uses include reconstructing total position cost from pay-element components, comparing budgeted versus actual compensation distributions, and reporting on benefit versus salary proportions by position or budget year.

A representative query summing element costs per position line:

• SELECT POSITION_LINE_ID, SUM(ELEMENT_COST) FROM PSB_WS_ELEMENT_LINES GROUP BY POSITION_LINE_ID;

Breakdown by pay element for a worksheet:

• SELECT l.POSITION_LINE_ID, p.NAME, l.ELEMENT_COST, l.CURRENCY_CODE FROM PSB_WS_ELEMENT_LINES l, PSB_PAY_ELEMENTS p WHERE l.PAY_ELEMENT_ID = p.PAY_ELEMENT_ID;

Related Objects

The table participates in several documented foreign key relationships:

• PSB_PAY_ELEMENTS – joined via PAY_ELEMENT_ID.
• PSB_WS_POSITION_LINES – joined via POSITION_LINE_ID; the primary parent of element lines.
• PAY_ELEMENT_SETS – joined via ELEMENT_SET_ID.
• PSB_SERVICE_PACKAGES – joined via SERVICE_PACKAGE_ID.

Together these relationships place PSB_WS_ELEMENT_LINES at the center of the Public Sector Budgeting worksheet cost model, linking positions, pay elements, element sets, and service packages for complete compensation decomposition.