Results for “psb_positions_i_u1”

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

Overview

PSB_POSITIONS_I is an interface (staging) table belonging to the Public Sector Budgeting (PSB) module, identified in the ETRM metadata as PSB - Public Sector Budgeting (Obsolete). As its description states, it serves as the interface table for PSB_POSITIONS. Interface tables of this type act as the landing zone for data extracted from external or upstream sources — most notably Oracle HRMS position and employee assignment data — before it is validated, transformed, and merged into the permanent PSB base table. In Oracle EBS 12.1.1, the object is owned by the PSB schema and is documented with 59 columns; the metadata notes that it is "Not implemented in this database," a status consistent with the obsolescence of the module in later releases and which should be confirmed against any given instance before development.

From a Data Vault modeling perspective, the ETRM heuristic classifies this table as standalone, meaning the mined foreign-key structure does not resolve it into a clean hub, link, or satellite. This is a modeling suggestion only; functionally, the table behaves like a staging satellite keyed against the upstream extract, position, and employee entities. It is transient in nature, designed to be populated by an extract process, consumed by a budget-position load, and then purged or reused per extract cycle.

Key Information Stored

The table's business content revolves around a small set of identity and control columns, supported by a large generated segment block.

• DATA_EXTRACT_ID — identifies the extract batch that populated the row; the primary grouping key for interface processing.
• HR_POSITION_ID and HR_EMPLOYEE_ID — the source HRMS position and employee assignment identifiers.
• HR_POSITION_NAME — the descriptive name of the budgeted position.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE and START_DATE_ACTIVE / END_DATE_ACTIVE — date-effective and active-range controls governing when the position is valid.
• ID_FLEX_NUM and SEGMENT1 through SEGMENT30 — the key flexfield structure number and its thirty reporting segments, which hold the account/position flexfield values.
• BUSINESS_GROUP_ID and ORGANIZATION_ID — the HR business group and inventory/HR organization context.
• TRANSACTION_ID, TRANSACTION_STATUS, AVAILABILITY_STATUS — workflow/processing control columns tracking the state of the interface row.
• Salary/grade attributes: SALARY_TYPE, RATE_OR_PAYSCALE_ID, GRADE_ID, GRADE_STEP, PAY_BASIS, VALUE.
• Audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE.

The documented surrogate/business-key candidate is the unique index PSB_POSITIONS_I_U1 on (HR_POSITION_ID, HR_EMPLOYEE_ID, DATA_EXTRACT_ID), which uniquely identifies a position-employee line within a single extract.

Common Use Cases and Queries

The table is used during budget-position interface loads. Typical reporting queries group by extract and check processing status:

• Extract line counts: SELECT data_extract_id, COUNT(*) FROM psb_positions_i GROUP BY data_extract_id;
• Unprocessed rows: filter on TRANSACTION_STATUS or AVAILABILITY_STATUS to find rows not yet merged into PSB_POSITIONS.
• Reconciliation of HRMS source: join on HR_POSITION_ID / HR_EMPLOYEE_ID to verify assignment coverage.
• Segment analysis: aggregate amounts (VALUE) across SEGMENT1–SEGMENT30 for budget distribution reporting.

Related Objects

• PSB_DATA_EXTRACTS — joined via PSB_POSITIONS_I.DATA_EXTRACT_ID = PSB_DATA_EXTRACTS.DATA_EXTRACT_ID; the parent extract definition.
• PSB_EMPLOYEE_ASSIGNMENTS_I — joined via HR_POSITION_ID; the assignment-level interface rows.
• PSB_EMPLOYEES_I — joined via HR_EMPLOYEE_ID; the employee interface rows.
• PSB_POSITIONS — the permanent base table for which this object is the interface.

Given the module's obsolete status, implementers should treat these relationships as historical and validate column and index presence against the specific 12.1.1 or 12.2.2 instance in use.