Results for “per_ass_status_type_amends”

50+ results




Overview
Explore in AI Chat

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

Overview

The HR.PER_ASS_STATUS_TYPE_AMENDS table stores user amendments to predefined assignment status types within Oracle E-Business Suite. Assignment status types constitute the foundation of the person-assignment lifecycle in Oracle HRMS, and Oracle ships a set of predefined status types (such as Active Assignment, End Assignment, and Suspend Assignment) as seed data. Because predefined rows cannot be modified directly, any customer-specific behavior — renaming the user-facing status, altering the active or default flags, or overriding the payroll or system status mapping — is captured in this amendments table. The table is owned by the HR schema and is valid in both EBS 12.1.1 and 12.2.2; in 12.2.2 the physical schema documents 14 columns and includes a ZD_EDITION_NAME column consistent with the editioning infrastructure used in that release.

The ETRM metadata classifies this object heuristically as a link under Data Vault modeling conventions. This classification reflects its structure: the row does not exist independently but associates a predefined assignment status type with a specific business group, effectively recording the pairing of those two business entities. Modelers building a Data Vault layer over EBS should therefore treat PER_ASS_STATUS_TYPE_AMENDS as a link table rather than a hub or satellite, though the descriptive attribute columns may be split into an accompanying satellite depending on requirements.

Key Information Stored

The documented primary key is ASS_STATUS_TYPE_AMEND_ID, a surrogate identifier generated for each amendment row. Two unique indexes define the business-key candidates: PER_ASS_STATUS_TYPE_AMENDS_PK (comprising ASS_STATUS_TYPE_AMEND_ID and ZD_EDITION_NAME) and PER_ASS_STATUS_TYPE_AMENDS_UK2 (comprising USER_STATUS, BUSINESS_GROUP_ID, and ZD_EDITION_NAME). The second index establishes that the user-visible status name must be unique within a business group.

Among the 14 documented columns, the most significant are:

  • ASS_STATUS_TYPE_AMEND_ID — surrogate primary key for the amendment record.
  • ASSIGNMENT_STATUS_TYPE_ID — foreign key to PER_ASSIGNMENT_STATUS_TYPES, identifying which predefined status type is being amended.
  • BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the amendment to a specific business group and enforcing multi-tenant separation of enterprise data.
  • USER_STATUS — the customer-defined display name substituted for the seeded status text; a business-key candidate component.
  • ACTIVE_FLAG — controls whether the amended status is selectable in assignment transactions.
  • DEFAULT_FLAG — indicates whether the status is proposed by default in the applicable business process.
  • PAY_SYSTEM_STATUS — the system status value passed to Oracle Payroll when this status is applied.
  • PER_SYSTEM_STATUS — the system status value used for core HR processing.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.
  • ZD_EDITION_NAME — editioning discriminator present in the 12.2.2 physical schema.

Common Use Cases and Queries

Typical queries resolve the amended display name for a status type within a business group, or audit which predefined statuses have been customized. A common reporting pattern joins the amendments back to the seeded definitions:

  • Listing all amendments for a business group: SELECT ass_status_type_amend_id, assignment_status_type_id, user_status FROM hr.per_ass_status_type_amends WHERE business_group_id = :p_bg_id;
  • Resolving display names: join ASSIGNMENT_STATUS_TYPE_ID to PER_ASSIGNMENT_STATUS_TYPES to compare the seeded USER_STATUS with the amended value.
  • Validating uniqueness conflicts against PER_ASS_STATUS_TYPE_AMENDS_UK2 before data load or migration.
  • Analyzing PAY_SYSTEM_STATUS and PER_SYSTEM_STATUS mappings to confirm downstream payroll and HR behavior after a status rename.

In 12.2.2 environments, queries should account for ZD_EDITION_NAME to avoid returning rows from inactive editions.

Related Objects

The documented foreign keys identify two principal parent tables, and additional dependents exist through the assignment status configuration chain:

  • PER_ASSIGNMENT_STATUS_TYPES — referenced via ASSIGNMENT_STATUS_TYPE_ID; holds the predefined status type definitions being amended.
  • HR_ALL_ORGANIZATION_UNITS — referenced via BUSINESS_GROUP_ID; provides the business group context.
  • PER_ASSIGNMENT_STATUS_TYPES related child tables and assignment status type usages, consulted when the amendment drives assignment-level behavior.
  • PER_ALL_ASSIGNMENTS_F — the primary assignment entity whose status values ultimately derive from the amended status configuration.
  • Human Resources assignment status forms and the corresponding status type setup windows, which read this table when rendering available statuses.
  • Public HRMS APIs used for assignment creation and update, which validate the selected status against this table and its parent definitions.