Results for “irc_assignment_statuses”

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

Overview

IRC_ASSIGNMENT_STATUSES is an Oracle E-Business Suite table owned by the HR schema and delivered under the PER (Human Resources) product family. Its documented purpose is to store assignment statuses for a particular applicant assignment. In practical terms, it functions as the transaction and history table for recruitment assignment status changes: each row records that a given applicant assignment transitioned to a particular status at a given point in time, together with the reason, comments, and the identity of the person making the change.

The table sits at the intersection of two functional areas. On one side it is anchored to Oracle iRecruitment applicant assignment data through the ASSIGNMENT_ID column; on the other it draws its valid status values from the PER_ASSIGNMENT_STATUS_TYPES lookup table in Oracle HRMS. It is also referenced by Oracle Task Manager tables (JTF_TASK_ASSIGNMENTS and JTF_TASK_CUSTOM_COLORS), which means the status identifiers defined here propagate into task-tracking and color-coding configurations.

The ETRM metadata carries a heuristic Data Vault classification of "satellite-leaning." As a modeling suggestion, this reflects the fact that IRC_ASSIGNMENT_STATUSES primarily describes and tracks the state of a parent assignment entity rather than defining a business hub or resolving a many-to-many link. The status history rows are therefore best treated as descriptive satellite records attached to the applicant assignment.

Key Information Stored

The table is documented with 13 columns in release 12.2.2. The most significant are:

• ASSIGNMENT_STATUS_ID — the surrogate primary key, enforced by the IRC_ASSIGNMENT_STATUSES_PK unique index. This is the only documented business-key candidate in the metadata.
• ASSIGNMENT_ID — the applicant assignment to which the status record belongs; the principal join column back to recruitment assignment data.
• ASSIGNMENT_STATUS_TYPE_ID — the foreign key to PER_ASSIGNMENT_STATUS_TYPES, identifying which status was applied.
• STATUS_CHANGE_DATE — the effective date and time of the status transition, central to any point-in-time reporting.
• STATUS_CHANGE_REASON — the coded reason for the change, useful for trend and attrition analysis.
• STATUS_CHANGE_COMMENTS — free-text commentary entered by the recruiter or HR user.
• STATUS_CHANGE_BY — the user responsible for the status change.
• CREATED_BY, CREATION_DATE — standard row creation audit columns.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard row modification audit columns.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Application Framework to detect concurrent updates.

Common Use Cases and Queries

Typical reporting scenarios include tracking the current status of an applicant assignment, reconstructing status history for audit purposes, and measuring time spent in each status for recruitment cycle-time analysis. A basic current-status query joins the table to the status type lookup and selects the latest row per assignment:

• SELECT ias.assignment_id, past.status_name, ias.status_change_date FROM irc_assignment_statuses ias, per_assignment_status_types past WHERE ias.assignment_status_type_id = past.assignment_status_type_id AND ias.status_change_date = (SELECT MAX(x.status_change_date) FROM irc_assignment_statuses x WHERE x.assignment_id = ias.assignment_id).
• Historical trend reporting filters on STATUS_CHANGE_DATE ranges and groups by ASSIGNMENT_STATUS_TYPE_ID to count transitions per period.
• Audit extracts use STATUS_CHANGE_BY and the CREATED_BY/LAST_UPDATED_BY columns to attribute changes to specific users.
• Task Manager integrations join the table to JTF_TASK_ASSIGNMENTS on ASSIGNMENT_STATUS_ID when the same status identifiers are reused for task tracking.

Related Objects

The following objects are the most significant dependencies:

• PER_ASSIGNMENT_STATUS_TYPES — parent lookup joined on ASSIGNMENT_STATUS_TYPE_ID; supplies the status name and meaning.
• JTF_TASK_ASSIGNMENTS — references this table via ASSIGNMENT_STATUS_ID for task status assignment.
• JTF_TASK_CUSTOM_COLORS — references this table via ASSIGNMENT_STATUS_ID for status color configuration.
• IRC_ASSIGNMENT_STATUSES_PK — the primary key unique index on ASSIGNMENT_STATUS_ID.
• Applicant and assignment tables in the iRecruitment/PER model that supply the ASSIGNMENT_ID parent context, queried through the assignment identifier.