Results for “hr_s_assignment_status_types”

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

Overview

The HR.HR_S_ASSIGNMENT_STATUS_TYPES table is a Human Resources (PER module) reference table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of assignment status types used throughout the Oracle HRMS application to categorize the lifecycle state of an employee assignment, such as Active, Terminated, Suspended, or Leave of Absence. The _S_ naming convention indicates a seed (non-translated) table that holds the base rows for the corresponding translated view, HR_ASSIGNMENT_STATUS_TYPES (typically HR_ASSIGNMENT_STATUS_TYPES_VL), which presents the language-specific name and description through the standard TL pattern.

The table is classified in the ETRM metadata with a heuristic Data Vault classification of standalone. In Data Vault modeling terms this suggests the object functions as a small reference or lookup hub, holding the durable set of assignment status type identifiers, with descriptive and control attributes that could be modeled as satellite data. Because it has a single documented foreign key to PER_ASSIGNMENT_STATUS_TYPES, the classification emphasizes its self-contained, lookup-oriented nature rather than participation in a larger link structure.

Key Information Stored

The documented physical schema contains 14 columns. The most significant are:

• ASSIGNMENT_STATUS_TYPE_ID — the surrogate primary key for the row, and also the foreign key to PER_ASSIGNMENT_STATUS_TYPES, tying the seed record to its base definition.
• BUSINESS_GROUP_ID — identifies the business group (HR operating unit) that owns the status type, enabling multi-tenant separation across enterprises.
• LEGISLATION_CODE — the legislation (country) under which the status type is valid, driving localization of HR behavior.
• ACTIVE_FLAG — whether the status type is currently enabled for use in assignment processing.
• DEFAULT_FLAG — indicates the default status type applied when a new assignment is created.
• PRIMARY_FLAG — marks whether the status is treated as a primary system status.
• USER_STATUS — the user-facing status value shown in HRMS forms and reports.
• PAY_SYSTEM_STATUS — the mapped internal value used by Oracle Payroll processing.
• PER_SYSTEM_STATUS — the mapped internal value used by the core Human Resources system.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard WHO audit columns required on all EBS tables.

The metadata does not document a unique business-key index, but ASSIGNMENT_STATUS_TYPE_ID is the primary key and the natural business candidate. The combination of BUSINESS_GROUP_ID, LEGISLATION_CODE, and the system status mappings effectively constitutes the semantic identity of a seeded row.

Common Use Cases and Queries

Typical usage includes HR reporting on headcount by status, validating status transitions in assignment APIs, and driving payroll eligibility rules. A common query pattern retrieves only active status types for a business group:

• SELECT assignment_status_type_id, user_status, per_system_status, pay_system_status
FROM hr.hr_s_assignment_status_types
WHERE business_group_id = :p_bg_id AND active_flag = 'Y';
• Join to the TL view to obtain the translated user status name: JOIN hr_assignment_status_types_vl atv ON atv.assignment_status_type_id = ...
• Reporting the default status for a legislation: ... WHERE default_flag = 'Y' AND legislation_code = :p_leg;

Related Objects

Because the table is standalone with one documented foreign key, the most significant related objects are:

• PER_ASSIGNMENT_STATUS_TYPES — parent table referenced via ASSIGNMENT_STATUS_TYPE_ID.
• HR_ASSIGNMENT_STATUS_TYPES_VL (and _TL) — translated views exposing the user status name.
• PER_ALL_ASSIGNMENTS_F — the assignment fact table that stores ASSIGNMENT_STATUS_TYPE_ID for each employee assignment.
• PER_ASSIGNMENT_STATUS_TYPES — base definition rows for the seeded statuses.
• PAY_ASSIGNMENT_ACTIONS and payroll action tables — consume PAY_SYSTEM_STATUS to drive payroll processing.
• HR_ASSIGNMENT_API (PL/SQL API) — uses PER_SYSTEM_STATUS and USER_STATUS when creating or updating assignments.