Search Results per_assignment_status_types

Overview

The PER_ASSIGNMENT_STATUS_TYPES table is a core reference table within the Oracle E-Business Suite Human Resources (HR) module. It serves as the master repository for all assignment status types, which are critical for defining the current working condition or employment status of an employee's assignment. The table stores both system-predefined statuses, such as 'Active' and 'Suspended', and user-defined statuses configured to meet specific organizational or legislative requirements. Its primary role is to provide a validated list of status codes that are referenced by employee assignment records, ensuring data integrity and consistency across HR and payroll processes. The table is owned by the HR schema and is integral to the assignment life cycle management.

Key Information Stored

The table's structure is designed to support multi-organization and legislative flexibility. Its key columns include the surrogate primary key, ASSIGNMENT_STATUS_TYPE_ID, which uniquely identifies each status record. The USER_STATUS column holds the display name of the status (e.g., 'Terminated'). The BUSINESS_GROUP_ID and LEGISLATION_CODE columns control the visibility and applicability of each status type, allowing for country-specific or business-unit-specific configurations. The combination of USER_STATUS, BUSINESS_GROUP_ID, and LEGISLATION_CODE is enforced as a unique key (PER_ASSIGNMENT_STATUS_TYPE_UK2), preventing duplicate status definitions within the same context.

Common Use Cases and Queries

This table is central to reporting and data validation. A common use case is generating a list of all active employees by joining to PER_ALL_ASSIGNMENTS_F. Administrators often query this table to review or maintain the list of available statuses. Sample queries include fetching all statuses for a specific business group or legislation, and identifying which assignment statuses are currently in use.

• Sample Query: List all assignment status types for a business group:
  SELECT user_status, assignment_status_type_id
  FROM per_assignment_status_types
  WHERE business_group_id = &business_group_id
  ORDER BY user_status;
• Sample Query: Find assignments with a specific status:
  SELECT paaf.assignment_number, past.user_status
  FROM per_all_assignments_f paaf,
  per_assignment_status_types past
  WHERE paaf.assignment_status_type_id = past.assignment_status_type_id
  AND past.user_status = 'Active'
  AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date;

Related Objects

As indicated by its foreign key relationships, PER_ASSIGNMENT_STATUS_TYPES is a parent table to several critical HR objects. The most significant relationship is with PER_ALL_ASSIGNMENTS_F, where each assignment record references a valid status type. It is also referenced by payroll rules (PAY_STATUS_PROCESSING_RULES_F), iRecruitment statuses (IRC_ASSIGNMENT_STATUSES), and amendment history (PER_ASS_STATUS_TYPE_AMENDS). Other dependent tables include PER_SECONDARY_ASS_STATUSES and PER_LETTER_REQUEST_LINES, highlighting its role in secondary assignments and HR correspondence.