Search Results irc_offer_status_history




Overview
Explore in AI Chat

Overview

The IRC_OFFER_STATUS_HISTORY table is a core data object within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically supporting the iRecruitment functionality. Its primary role is to serve as an audit and tracking mechanism for the lifecycle of job offers made to candidates. In the context of Oracle EBS 12.1.1 and 12.2.2, this table is essential for maintaining a complete historical record of all status changes an offer undergoes, from initial creation through acceptance, rejection, or withdrawal. This historical data is critical for compliance, process analysis, and providing a transparent audit trail for recruitment activities.

Key Information Stored

The table's structure is designed to capture the essential details of each status transition. While the full column list is not detailed in the provided metadata, the documented keys and relationships indicate the critical data points. The primary key, OFFER_STATUS_HISTORY_ID, uniquely identifies each status history record. The OFFER_ID foreign key column links each history entry to its parent record in the IRC_OFFERS table, which contains the core offer details. The table will inherently store columns for the status value (e.g., 'EXTENDED', 'ACCEPTED', 'REJECTED'), the date and time of the change, and the identifier of the user or process that initiated the change. The foreign key relationship to the IRC_NOTES table suggests the structure supports attaching explanatory notes to specific status transitions.

Common Use Cases and Queries

This table is primarily used for auditing and analytical reporting. A common operational use case is to generate a timeline of events for a specific job offer, often required for managerial review or candidate dispute resolution. For reporting, HR analysts query this table to measure recruitment process efficiency, such as calculating the average time an offer spends in each status or identifying bottlenecks. A typical query pattern involves joining with IRC_OFFERS and PER_ALL_PEOPLE_F to analyze offer history by candidate or job.

  • Sample Query (Offer Timeline): SELECT history.STATUS_CODE, history.CREATION_DATE FROM HR.IRC_OFFER_STATUS_HISTORY history WHERE history.OFFER_ID = :offer_id ORDER BY history.CREATION_DATE;
  • Sample Query (Process Analysis): SELECT offer.OFFER_NUMBER, MIN(hist.CREATION_DATE) AS OFFER_EXTENDED, MAX(hist.CREATION_DATE) AS FINAL_STATUS_DATE FROM IRC_OFFERS offer JOIN IRC_OFFER_STATUS_HISTORY hist ON offer.OFFER_ID = hist.OFFER_ID GROUP BY offer.OFFER_NUMBER;

Related Objects

The IRC_OFFER_STATUS_HISTORY table is centrally linked within the iRecruitment schema. Its primary relationship is with the IRC_OFFERS table, which holds the master offer record. Each status history entry is a child of a single parent offer. Furthermore, the table has a one-to-many relationship with the IRC_NOTES table, allowing multiple notes to be associated with a single status change event. This is frequently used to document reasons for a status update, such as negotiation terms or candidate feedback. Any custom reports or integrations tracking the offer lifecycle will invariably reference this history table in conjunction with its related entities.