Results for “hr_position_id”

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

Overview

PSB_EMPLOYEE_ASSIGNMENTS_I is an interface table within the PSB (Public Sector Budgeting) product family of Oracle E-Business Suite, documented as VALID in both the 12.1.1 and 12.2.2 releases. The table serves as the staging and interface structure for Position assignments, capturing the association between employees and the positions they occupy during the budgeting and extract lifecycle. As an interface object, it is not the system of record for position assignment data but rather a transient repository populated by data extraction processes and consumed by downstream budgeting logic.

From a Data Vault modeling perspective, the metadata's heuristic classification identifies this object as a link. This is consistent with its structural role: the table resolves the many-to-many relationship between PSB_EMPLOYEES_I (employee extract records) and the position extract records, rather than holding a standalone business entity. Modelers should treat the association and its descriptive attributes as candidates for a link construct with a small satellite for the effective-dating and audit columns.

Key Information Stored

The documented physical schema for 12.1.1 lists twelve columns. The most consequential columns are:

• HR_POSITION_ID — The primary key column of PSB_EMPLOYEE_ASSIGNMENTS_I_PK. It is the surrogate identifier that downstream PSB tables reference, and is the single documented column composing the unique key.
• HR_EMPLOYEE_ID — Foreign key to PSB_EMPLOYEES_I, identifying the employee whose assignment is staged.
• DATA_EXTRACT_ID — Foreign key to PSB_DATA_EXTRACTS, tying each assignment row to a specific extraction batch and enabling run-level traceability and reprocessing.
• ATTRIBUTE_NAME and ATTRIBUTE_VALUE — A generic name/value pair pattern used to carry assignment attributes in the interface without committing to a fixed wide-column layout.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — Date-tracked validity of the assignment, supporting as-of analysis and extract-period filtering.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — Standard EBS audit columns used for concurrency, reconciliation, and interface diagnostics.

The surrogate PK is HR_POSITION_ID from the perspective of this table's own unique index; business-key uniqueness across HR_EMPLOYEE_ID, DATA_EXTRACT_ID, EFFECTIVE_START_DATE, and ATTRIBUTE_NAME would normally be asserted by the load process rather than a documented database constraint.

Common Use Cases and Queries

Typical usage centers on validating what an extract loaded, reconciling assignments per employee, and diagnosing interface failures before budgeting processes consume the data. A representative query lists assignments for a given extract:

• SELECT a.HR_POSITION_ID, a.HR_EMPLOYEE_ID, a.ATTRIBUTE_NAME, a.ATTRIBUTE_VALUE, a.EFFECTIVE_START_DATE, a.EFFECTIVE_END_DATE FROM PSB.PSB_EMPLOYEE_ASSIGNMENTS_I a WHERE a.DATA_EXTRACT_ID = :extract_id
• Join to PSB_EMPLOYEES_I on HR_EMPLOYEE_ID to resolve employee detail for reporting.
• Count assignments grouped by DATA_EXTRACT_ID to detect partial or duplicate loads.
• Effectivity filtering with SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE) for point-in-time position staffing views.

Related Objects

The table participates in a dense PSB relationship network. It references PSB_EMPLOYEES_I via HR_EMPLOYEE_ID and PSB_DATA_EXTRACTS via DATA_EXTRACT_ID. Conversely, HR_POSITION_ID is referenced by PSB_EMPLOYEES_I, PSB_POSITION_ACCOUNTS, PSB_POSITIONS_I, PSB_POSITION_COSTS, PSB_POSITIONS, and PSB_POSITION_FTE. Analysts should join through HR_POSITION_ID to reach position cost, FTE, and account distributions, and through DATA_EXTRACT_ID to scope results to a specific extraction run.