Search Results hr_employee_id

Overview

The PSB_EMPLOYEES_I table is an interface table within the Oracle E-Business Suite (EBS) Public Sector Budgeting (PSB) module, which is documented as obsolete. Its primary role is to serve as a staging area for employee data during data integration or migration processes. Specifically, it acts as the intermediary table for loading data into the base PSB_EMPLOYEES table. Interface tables like this are fundamental to EBS data architecture, providing a standardized structure for validating and transforming external data before it is formally processed and transferred into the application's core transactional tables. The "I" suffix is a standard Oracle naming convention denoting an interface table.

Key Information Stored

Based on the provided ETRM metadata, the table's structure centers on a primary key column, HR_EMPLOYEE_ID, which uniquely identifies employee records within the interface. A critical foreign key column is DATA_EXTRACT_ID, which links to the PSB_DATA_EXTRACTS table. This relationship indicates that records in PSB_EMPLOYEES_I are associated with a specific data extraction or load batch, allowing for grouped processing and auditability. While the full column list is not detailed, typical interface tables in this context would also include columns mirroring the target table (PSB_EMPLOYEES), such as employee name, assignment details, and effective dates, along with control columns for interface status, error messages, and processing flags.

Common Use Cases and Queries

The principal use case for PSB_EMPLOYEES_I is the batch import of employee information into the Public Sector Budgeting module from external HR systems or legacy databases. Data would be inserted into this table via custom scripts, SQL*Loader, or other ETL tools. Common operational queries would involve checking the status of interface records. For example, a query to identify unprocessed records would be: SELECT * FROM PSB_EMPLOYEES_I WHERE PROCESS_STATUS IS NULL AND DATA_EXTRACT_ID = <extract_id>;. Another typical pattern is joining with the PSB_DATA_EXTRACTS table to analyze load batches: SELECT de.EXTRACT_NAME, COUNT(*) FROM PSB_EMPLOYEES_I ei, PSB_DATA_EXTRACTS de WHERE ei.DATA_EXTRACT_ID = de.DATA_EXTRACT_ID GROUP BY de.EXTRACT_NAME;. After validation, a concurrent program or custom PL/SQL procedure would be executed to transfer the data from this interface to the base table.

Related Objects

The ETRM documentation explicitly defines the following key relationships for the PSB_EMPLOYEES_I table:

• Primary Key: The table is uniquely identified by the HR_EMPLOYEE_ID column.
• Foreign Key (Parent): PSB_EMPLOYEES_I references the PSB_DATA_EXTRACTS table via the DATA_EXTRACT_ID column. This establishes the batch context for the interface data.
• Foreign Key (Child): The PSB_EMPLOYEE_ASSIGNMENTS_I interface table references PSB_EMPLOYEES_I using the HR_EMPLOYEE_ID column. This indicates a hierarchical data load where employee assignments are staged separately but linked to the core employee record in the interface layer.

The ultimate target for the data in this interface is the base table PSB_EMPLOYEES, as stated in the table description.