Results for “psb_position_definitions”

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

Overview

PSB.PSB_POSITION_DEFINITIONS is a key flexfield definition table within the PSB – Public Sector Budgeting module of Oracle E-Business Suite. Its documented purpose is to store position key flexfield information; in practice it defines the valid position key flexfield (KFF) combinations used as budget entities throughout the position budgeting process. Each row represents one position definition — a single, uniquely identified combination of position segments — against which position budgets, salary and fringe calculations, and workforce-planning allocations are staged and then loaded to the General Ledger or Budget module.

From a Data Vault modeling perspective, this object is classified heuristically as hub-leaning. That classification reflects the structure of the table: POSITION_DEFINITION_ID is a stable, single-column surrogate key with no composite or business-key-driven grain, and dependent descriptive attributes (segment values, active-date ranges, enabled/summary flags, audit columns) are held on the same row. In a Data Vault 2.0 design this object would typically be modelled as a hub for position definitions rather than as a link table, with the descriptive attributes optionally split into a satellite. This is a suggestion for integration or warehouse design only; the native EBS table is a conventional, flexfield-backed entity table.

Key Information Stored

The table is documented with 41 columns in release 12.1.1, of which the following are the most significant:

• POSITION_DEFINITION_ID — the surrogate primary key, enforced by the PSB_POSITION_DEFINITIONS_PK constraint. It is a system-generated identifier and is the sole documented unique index on the table.
• ID_FLEX_NUM — identifies the key flexfield structure (the frozen, versioned definition of the Position flexfield) to which this combination belongs. Because the same segment set can exist under more than one flexfield structure, ID_FLEX_NUM is an essential discriminator for any business-key reasoning.
• SEGMENT1 … SEGMENT30 — the thirty flexfield segment columns that carry the actual position identifier, for example agency, organization, position type, and position number. The populated subset depends on the segments enabled in the deployed Position flexfield structure; unused segments remain null. Together with ID_FLEX_NUM, the concatenated segments form the effective business-key candidate for a position definition.
• ENABLED_FLAG — indicates whether the definition is currently available for selection in budgeting pages and validation APIs.
• SUMMARY_FLAG — marks a segment value as a summary (roll-up parent) rather than a detail (postable) position, supporting hierarchical reporting.
• START_DATE_ACTIVE and END_DATE_ACTIVE — define the date range during which the definition is valid, allowing historical and future-dated position structures.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS WHO (audit) columns, used for change tracking, extract filtering, and data lineage in warehouse loads.

Common Use Cases and Queries

The principal use case is resolving a concatenated position flexfield string into a definition identifier, or the reverse, when reconciling position budget data. A typical lookup given a segment set is:

• SELECT position_definition_id FROM psb_position_definitions WHERE id_flex_num = :p_flex AND segment1 = :p_seg1 AND segment2 = :p_seg2 AND enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);

Related scenarios include validating which definitions are active as of a budget cycle date, identifying summary versus detail positions for roll-up reporting, and driving incremental extracts by filtering on LAST_UPDATE_DATE and LAST_UPDATED_BY. Reporting extracts frequently join this table to the flexfield definitions (FND_ID_FLEX_STRUCTURES, FND_ID_FLEX_SEGMENTS) to translate segment numbers into segment names, and to PSB_POSITIONS to count the position records attached to each definition. Because the table is key-flexfield driven, queries should always constrain on ID_FLEX_NUM to avoid cross-structure collisions.

Related Objects

The documented foreign-key relationship identifies the following dependencies:

• PSB_POSITIONS — PSB_POSITIONS.POSITION_DEFINITION_ID references PSB_POSITION_DEFINITIONS.POSITION_DEFINITION_ID. This is the primary transactional child table; each position row is anchored to exactly one position definition.
• FND_ID_FLEX_STRUCTURES — joins on ID_FLEX_NUM to supply the flexfield structure title and configuration.
• FND_ID_FLEX_SEGMENTS — joins on ID_FLEX_NUM and segment number to resolve segment names, prompts, and enabled flags for reporting.
• FND_FLEX_VALUES / FND_FLEX_VALUES_TL — provide the description and translated label for each populated segment value.
• PSB_BUDGET_ACCOUNTS (Position Budgeting tables in the PSB schema) — consume the resolved definition identifier when staging position budget lines.
• GL_CODE_COMBINATIONS — the downstream account combination generated when position budget data is transferred to General Ledger, correlated through the segment values held here.

These relationships make PSB_POSITION_DEFINITIONS a hub-like reference point for position budgeting: it is referenced by downstream transactional tables and, in turn, depends on the shared Applications flexfield definition tables for its structure and value validation.