Results for “per_assignment_status_types”

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

Overview

PER_ASSIGNMENT_STATUS_TYPES is a Human Resources (PER) reference table in the HR schema of Oracle E-Business Suite, holding both Oracle-seeded and customer-defined assignment status types. Assignment status is the core attribute that determines whether an employee or contingent worker is active, inactive, suspended, terminated, or in any other lifecycle state, and it directly governs payroll processing, benefits eligibility, and reporting semantics. Each row defines a status code together with its behavioral flags and legislation context.

As a reference or lookup table with a single-column surrogate primary key and no composite child dependencies of its own, the object is best modeled as a hub in Data Vault terms — a stable, business-key-bearing entity referenced by many surrounding transactional and association tables. In ETRM 12.2.2 the table documents 15 columns; the table exists with identical semantics in 12.1.1, though 12.2.2 adds the editioning column ZD_EDITION_NAME to support Online Patching and multi-tenant/editioned data.

Key Information Stored

The surrogate primary key is ASSIGNMENT_STATUS_TYPE_ID, which uniquely identifies each status row and is the value propagated to every dependent table. Business-key uniqueness for user-defined rows is enforced through the unique index PER_ASSIGNMENT_STATUS_TYPE_UK2 on USER_STATUS, BUSINESS_GROUP_ID, LEGISLATION_CODE, and ZD_EDITION_NAME, ensuring a status name is not duplicated within a business group and legislation.

• BUSINESS_GROUP_ID — scopes the status to a specific business group; seeded Oracle statuses typically carry a null or global value, while customer-defined statuses are group-specific.
• LEGISLATION_CODE — the country or legislation under which the status is valid, enabling country-specific status behaviour.
• USER_STATUS — the user-facing status name (e.g., Active, Suspended, Terminated) and the primary business-key attribute.
• ACTIVE_FLAG — indicates whether the status type is available for selection and use.
• DEFAULT_FLAG — identifies the status assigned by default when a new assignment is created.
• PRIMARY_FLAG — distinguishes primary assignment statuses from secondary statuses.
• PAY_SYSTEM_STATUS — the payroll system status (e.g., Processing, Not Processing) that the assignment status maps to.
• PER_SYSTEM_STATUS — the HR system status (e.g., Active Assignment, Inactive Assignment) that the assignment status maps to.

Standard audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — track the creation and modification history of each row.

Common Use Cases and Queries

The table is used to validate and decode assignment status values, to drive payroll and benefits rules, and to populate status-level reporting. A typical lookup joins the assignment to this table to resolve the human-readable status name:

• Decoding current assignments: join PER_ALL_ASSIGNMENTS_F to PER_ASSIGNMENT_STATUS_TYPES on ASSIGNMENT_STATUS_TYPE_ID to show USER_STATUS for each active assignment.
• Filtering by payroll status: query rows where PAY_SYSTEM_STATUS indicates processing, to identify which assignment statuses drive payroll.
• Identifying defaults: select statuses where DEFAULT_FLAG = 'Y' and ACTIVE_FLAG = 'Y' within a given BUSINESS_GROUP_ID and LEGISLATION_CODE.
• Reporting by HR system status: group headcount counts by PER_SYSTEM_STATUS to reconcile active versus inactive populations.
• Data migration and setup: catalog user-defined statuses (rows where a user-defined superset of the unique key matches) before loading or interfacing with third-party systems.

Because dependent tables such as PER_ALL_ASSIGNMENTS_F store only the identifier, virtually every status-based report must join back to this table for descriptive context.

Related Objects

Numerous tables reference this object through ASSIGNMENT_STATUS_TYPE_ID. The most significant include:

• PER_ALL_ASSIGNMENTS_F — the primary transactional assignments table; ASSIGNMENT_STATUS_TYPE_ID drives current and historical status.
• PER_SECONDARY_ASS_STATUSES — defines secondary statuses applicable to an assignment.
• PER_ASS_STATUS_TYPE_AMENDS — governs amendments and the statuses that permit them.
• PAY_STATUS_PROCESSING_RULES_F — links assignment status to payroll processing rules.
• BEN_ELIG_EE_STAT_PRTE_F and BEN_EE_ST_RT_F — benefits eligibility driven by assignment status.
• IRC_ASSIGNMENT_STATUSES — iRecruitment status mapping.
• PER_LETTER_REQUEST_LINES and PER_LETTER_GEN_STATUSES — letter generation tied to status transitions.
• PER_ABSENCE_ATTENDANCE_TYPES — absence types permitted for a given assignment status.
• HR_S_ASSIGNMENT_STATUS_TYPES and related EDW/HR staging objects — extract and warehouse views for reporting.