Search Results per_ass_status_type_amends

Overview

The PER_ASS_STATUS_TYPE_AMENDS table is a core data object within the Oracle E-Business Suite Human Resources (HR) module. It serves as a repository for user-defined modifications to the system's predefined assignment status types. Assignment status types are critical for classifying an employee's work relationship (e.g., Active, Terminated, Suspended). This table allows organizations to customize the displayed status name (USER_STATUS) for these predefined types to align with internal terminology, without altering the underlying system logic. Its role is to provide a flexible layer of localization and customization on top of the seeded HRMS foundation data, ensuring the system reflects organizational language while maintaining referential integrity with core transactional tables.

Key Information Stored

The table stores the mapping between a predefined system status and its user-defined label within a specific business group. Key columns include:

• ASS_STATUS_TYPE_AMEND_ID: The primary key identifier for each amendment record.
• ASSIGNMENT_STATUS_TYPE_ID: A foreign key linking to the predefined status in PER_ASSIGNMENT_STATUS_TYPES.
• USER_STATUS: The customized, user-friendly name for the status as displayed in the application.
• BUSINESS_GROUP_ID: A foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the amendment to a specific business group, enabling multi-org customization.

The unique key constraint on USER_STATUS and BUSINESS_GROUP_ID ensures that a custom status name is unique within a given business group.

Common Use Cases and Queries

A primary use case is generating reports or building interfaces that require the display of assignment statuses using company-specific terminology. For instance, an organization may have amended the system status "Terminated" to read "Employment Ended." A common query retrieves the custom status for a given assignment by joining through the PER_ASSIGNMENTS table. A typical SQL pattern is:

SELECT pa.assignment_number,
       NVL(pasta.user_status, past.assignment_status_type) AS display_status
FROM per_assignments pa,
     per_assignment_status_types past,
     per_ass_status_type_amends pasta
WHERE pa.assignment_status_type_id = past.assignment_status_type_id
  AND past.assignment_status_type_id = pasta.assignment_status_type_id(+)
  AND pa.business_group_id = pasta.business_group_id(+);

This query ensures the user-amended status is displayed where it exists, falling back to the system default. Administrators may also query this table directly to audit or manage customizations across business groups.

Related Objects

This table is centrally connected to several key HRMS objects:

• PER_ASSIGNMENT_STATUS_TYPES: The primary foreign key relationship, sourcing the base system statuses that are being amended.
• HR_ALL_ORGANIZATION_UNITS: Provides the BUSINESS_GROUP_ID, enforcing security and data segregation.
• PER_ASSIGNMENTS: The primary transactional table that references assignment status types; the amendments in this table affect how statuses are displayed for records in PER_ASSIGNMENTS.
• Key Views: Underlying HRMS application views, such as those supporting Assignment forms and HRMS reports, will incorporate logic to select the amended USER_STATUS where applicable.

Direct data manipulation in this table is not recommended; changes are typically performed via the Oracle HRMS application interface to maintain validation and business logic.