Search Results per_secondary_ass_statuses




Overview
Explore in AI Chat

Overview

The PER_SECONDARY_ASS_STATUSES table is a core data structure within the Oracle E-Business Suite Human Resources (PER) module, specifically for releases 12.1.1 and 12.2.2. It serves as a master reference table for defining and storing secondary assignment statuses. These statuses provide an additional, supplementary layer of categorization for an employee's assignment, beyond the primary assignment status. The table's primary role is to support enhanced information tracking, detailed reporting, and business rule enforcement related to workforce assignments. It functions as a controlled lookup, ensuring data integrity and consistency across the HR system for secondary status information.

Key Information Stored

The table stores metadata that defines each valid secondary assignment status. While the full column list is not detailed in the provided metadata, the documented foreign keys and primary key reveal its critical components. The SECONDARY_ASS_STATUS_ID column is the unique primary key identifier for each status record. The ASSIGNMENT_STATUS_TYPE_ID is a foreign key linking to the PER_ASSIGNMENT_STATUS_TYPES table, establishing the relationship between a secondary status and its corresponding primary assignment status type (e.g., Active, Terminated). The BUSINESS_GROUP_ID foreign key links to HR_ALL_ORGANIZATION_UNITS, enforcing that secondary statuses are defined within the security and operational context of a specific business group. Other typical columns would include fields for the status name (e.g., SECONDARY_ASS_STATUS_NAME), effective start and end dates, and descriptive flexfield structures.

Common Use Cases and Queries

This table is central to queries that segment or analyze the workforce based on nuanced assignment conditions. A common use case is generating reports that list all employees with a specific primary status (like "Active") but a particular secondary status (such as "Leave of Absence" or "Sabbatical"). It is also critical for validation logic within forms and APIs when updating an assignment's secondary status. A typical reporting query would join this table to the primary assignment table (PER_ALL_ASSIGNMENTS_F) via the assignment status type.

  • Sample SQL Pattern: SELECT paaf.assignment_number, psast.assignment_status_type_code, psas.secondary_ass_status_name FROM per_all_assignments_f paaf, per_assignment_status_types psast, per_secondary_ass_statuses psas WHERE paaf.assignment_status_type_id = psast.assignment_status_type_id AND psast.assignment_status_type_id = psas.assignment_status_type_id AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND psas.secondary_ass_status_name = 'Long-Term Disability';

Related Objects

The PER_SECONDARY_ASS_STATUSES table maintains defined relationships with other key HR tables, as per the provided metadata. Its integrity is enforced through these relationships.

  • HR_ALL_ORGANIZATION_UNITS: Linked via the BUSINESS_GROUP_ID column. This ensures every secondary status is scoped to a valid business group, supporting multi-org implementations.
  • PER_ASSIGNMENT_STATUS_TYPES: Linked via the ASSIGNMENT_STATUS_TYPE_ID column. This is the most critical relationship, tethering each secondary status definition to a primary assignment status type, such as 'Active' or 'Suspended'.

While not listed in the provided excerpt, this table is also a likely parent to columns in transactional tables like PER_ALL_ASSIGNMENTS_F, which would store a FOREIGN KEY to SECONDARY_ASS_STATUS_ID to record the status on an employee's assignment.