Search Results hr_position_id




Overview
Explore in AI Chat

Overview

The PSB_EMPLOYEE_ASSIGNMENTS_I table is a core interface table within the Oracle E-Business Suite (EBS) Public Sector Budgeting (PSB) module. Its primary role is to serve as the staging area for employee-specific position attribute data extracted from an external Human Resource Management System (HRMS). The table holds transformed assignment data with respect to the employee, which is subsequently processed to create or update position assignments in the PSB_POSITION_ASSIGNMENTS table. This interface mechanism is critical for integrating HR data with budgetary planning and position control, ensuring financial structures reflect current organizational staffing.

Key Information Stored

The table stores the linkage between employees, their assigned positions, and the specific attributes of those positions. Key columns include the HR_POSITION_ID, which is the unique identifier for the position in the source HR system and serves as the table's primary key. The HR_EMPLOYEE_ID uniquely identifies the employee from the source system. Together, these columns define the core assignment relationship. The ATTRIBUTE_NAME and ATTRIBUTE_VALUE columns store the specific position characteristics (e.g., grade, step, funding source) applicable to the employee. Temporal validity is managed through EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. The DATA_EXTRACT_ID ties all records to a specific data load batch from the source system, facilitating traceability and error handling.

Common Use Cases and Queries

A primary use case is validating and troubleshooting data loads from the HRMS into PSB. Analysts often query this interface table to verify that extracted employee assignments have been correctly staged before running the subsequent process to populate PSB_POSITION_ASSIGNMENTS. A common query pattern involves filtering by a specific data extract or employee to review all position attributes. For example, to audit all pending assignments for a particular employee:

  • SELECT * FROM psb.psb_employee_assignments_i WHERE hr_employee_id = <employee_id> AND data_extract_id = <extract_id>;

Another frequent reporting need is to identify employees assigned to a specific position over time, which supports position funding and headcount analysis:

  • SELECT hr_employee_id, attribute_name, attribute_value, effective_start_date, effective_end_date FROM psb.psb_employee_assignments_i WHERE hr_position_id = <position_id> ORDER BY effective_start_date;

Related Objects

PSB_EMPLOYEE_ASSIGNMENTS_I is centrally connected to other PSB interface tables through defined foreign key relationships, forming a key part of the HR data integration flow. It references the PSB_EMPLOYEES_I table via the HR_EMPLOYEE_ID column, ensuring that only extracted employees have assignment records. It also references the PSB_DATA_EXTRACTS table via the DATA_EXTRACT_ID column, linking all interface data to a master extract record for lifecycle management. The table's ultimate destination is the PSB_POSITION_ASSIGNMENTS table, for which it serves as the direct source, though this is a procedural rather than a declarative foreign key relationship.