Results for “offer_letter”

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

Overview

IRC_OFFERS is the core transactional table in the Oracle E-Business Suite iRecruitment module (product code PER — Human Resources, owner schema HR). It stores the details of employment offers extended to applicants through the iRecruitment self-service flow. Each row represents a single version of an offer letter associated with a specific applicant assignment and vacancy, capturing the terms, status, distribution method, and generated document content of that offer.

Because offers are versioned, the table functions as a versioned satellite rather than a static master. A heuristic Data Vault classification of satellite-leaning is therefore a reasonable modeling suggestion: the table carries descriptive, time-variant state (status, dates, letter content) that changes as an offer is revised, extended, accepted, or withdrawn, and it hangs off business keys supplied by other hubs such as the applicant assignment and the vacancy.

Key Information Stored

The documented physical schema contains 57 columns. The most significant are:

• OFFER_ID — the surrogate primary key, enforced through the IRC_OFFERS_PK unique index. Every version of an offer is a distinct OFFER_ID.
• APPLICANT_ASSIGNMENT_ID and OFFER_VERSION — together they form the IRC_OFFERS_U1 unique index, the business-key candidate that identifies the version number of an offer for a given applicant.
• LATEST_OFFER — flag indicating which version is the currently active offer.
• OFFER_STATUS — the current lifecycle state of the offer (for example extended, accepted, or declined).
• VACANCY_ID — the requisition/vacancy against which the offer is made.
• RESPONDENT_ID — the FND_USER who is expected to respond to the offer.
• ADDRESS_ID — the address to which the offer letter is sent.
• TEMPLATE_ID — the XDO_TEMPLATES_B template used to render the offer letter.
• OFFER_EXTENDED_METHOD, OFFER_POSTAL_SERVICE, OFFER_SHIPPING_DATE, OFFER_LETTER_TRACKING_CODE — the delivery channel for postal offers and its tracking information.
• PROPOSED_START_DATE and EXPIRY_DATE — the intended start date and the deadline for the applicant's response.
• DISCRETIONARY_JOB_TITLE — an override or free-text title for the offer.
• OFFER_LETTER, OFFER_LETTER_FILE_NAME, OFFER_LETTER_FILE_TYPE — the stored/generated letter content and its file metadata.
• ATTRIBUTE1–ATTRIBUTE30 and OBJECT_VERSION_NUMBER — the standard EBS descriptive flexfield columns and optimistic locking column.

The surrogate key (OFFER_ID) is the technical identifier, while APPLICANT_ASSIGNMENT_ID plus OFFER_VERSION is the meaningful business key; the two must not be conflated in reporting.

Common Use Cases and Queries

Typical uses include offer-status dashboards, time-to-acceptance analytics, and letter audit trails. To retrieve the latest active offer for each applicant:

• SELECT offer_id, applicant_assignment_id, vacancy_id, offer_status, proposed_start_date FROM irc_offers WHERE latest_offer = 'Y';
• Join to HR.IRC_OFFER_STATUS_HISTORY on OFFER_ID to reconstruct the full status audit trail of an offer.
• Join to PER_ALL_VACANCIES on VACANCY_ID to report offers by requisition or recruiter.
• Join to FND_USER on RESPONDENT_ID and to PER_ADDRESSES on ADDRESS_ID for correspondence reporting.
• Filter by OFFER_EXTENDED_METHOD or EXPIRY_DATE to monitor pending or expired offers.

Related Objects

• IRC_OFFER_STATUS_HISTORY — child table keyed by OFFER_ID, recording each status transition.
• PER_ADDRESSES — referenced via ADDRESS_ID for the delivery address.
• FND_USER — referenced via RESPONDENT_ID for the responding user.
• XDO_TEMPLATES_B — referenced via TEMPLATE_ID for the letter template.
• PER_ALL_VACANCIES — referenced via VACANCY_ID for the vacancy details.

These relationships confirm IRC_OFFERS as the central offer hub within iRecruitment, linking applicant, vacancy, template, and correspondence data.