Results for “per_all_vacancies”

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

Overview

PER_ALL_VACANCIES is a core Human Resources table in the Oracle EBS PER product schema (owner: HR), available in both release 12.1.1 and 12.2.2. It stores the vacancies that exist within a specific requisition, effectively representing the individual openings that an organization is attempting to fill. A single requisition (PER_REQUISITIONS) can be associated with multiple vacancies, and each vacancy is the anchor point for downstream recruitment activity such as candidate considerations, offers, recruiting team membership, agency submissions, and posting assignments.

Under the heuristic Data Vault classification mined from the foreign key structure, PER_ALL_VACANCIES behaves as a hub: it carries a single-column surrogate primary key (VACANCY_ID) and is referenced by many dependent tables. This classification should be treated as a modeling suggestion rather than a prescriptive design constraint.

Key Information Stored

The physical schema in 12.2.2 documents 56 columns. The most significant are summarized below.

• VACANCY_ID — surrogate primary key, enforced by index PER_VACANCIES_PK.
• REQUISITION_ID — foreign key to PER_REQUISITIONS, identifying the parent requisition. Combined with NAME it forms the unique business key PER_VACANCIES_UK2.
• NAME — the identifying name of the vacancy; part of the UK2 business-key candidate.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, partitioning data by business group.
• ORGANIZATION_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, the organization owning the vacancy.
• POSITION_ID, JOB_ID, GRADE_ID — foreign keys to PER_ALL_POSITIONS, PER_JOBS, and PER_GRADES respectively, defining the position, job, and grade of the opening.
• LOCATION_ID — foreign key to HR_LOCATIONS_ALL, the work location.
• PEOPLE_GROUP_ID — foreign key to PAY_PEOPLE_GROUPS, the people group assigned to the vacancy.
• RECRUITER_ID / MANAGER_ID — the recruiter and hiring manager associated with the vacancy.
• DATE_FROM / DATE_TO — effective dating window for the vacancy.
• NUMBER_OF_OPENINGS — the count of positions to be filled under this vacancy.
• STATUS — the current state of the vacancy.
• VACANCY_CATEGORY, VACANCY_REGION_TYPE, SECURITY_METHOD — classification and access-control attributes.
• DESCRIPTION / COMMENTS — descriptive text for the vacancy.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER for concurrency control.
• DDF columns — ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 for descriptive flexfield data.

Common Use Cases and Queries

Recruitment reporting commonly joins vacancies to their parent requisition, organization, job, and position to produce open-position reports and time-to-fill metrics. A frequently used pattern consolidates the descriptive context:

SELECT v.vacancy_id, v.name, v.status, v.number_of_openings, v.date_from, v.date_to, r.requisition_id, o.name organization
FROM per_all_vacancies v, per_requisitions r, hr_all_organization_units o
WHERE v.requisition_id = r.requisition_id
AND v.organization_id = o.organization_id;

Other common scenarios include reconciling vacancies to filled assignments via PER_ALL_ASSIGNMENTS_F.VACANCY_ID, tracking offers through IRC_OFFERS, auditing recruiting team assignments in IRC_REC_TEAM_MEMBERS, and extracting vacancy listings into the HR EDW staging tables such as EDW_HR_RQVC_VACANCY_LSTG. Because VACANCY_ID is a foreign key on several transactional recruiting tables, vacancy-level metrics can be rolled up by requisition, organization, or recruiter.

Related Objects

• PER_REQUISITIONS — parent requisition, joined on REQUISITION_ID.
• PER_ALL_ASSIGNMENTS_F — assignments filled against the vacancy, joined on VACANCY_ID.
• IRC_OFFERS — offers extended for the vacancy, joined on VACANCY_ID.
• IRC_VACANCY_CONSIDERATIONS — candidate considerations, joined on VACANCY_ID.
• IRC_REC_TEAM_MEMBERS — recruiting team membership, joined on VACANCY_ID.
• IRC_AGENCY_VACANCIES — agency submissions, joined on VACANCY_ID.
• PER_RECRUITMENT_ACTIVITY_FOR — recruitment activities, joined on VACANCY_ID.
• HR_ALL_ORGANIZATION_UNITS, PER_ALL_POSITIONS, PER_JOBS, PER_GRADES, HR_LOCATIONS_ALL, PAY_PEOPLE_GROUPS — referenced dimension tables supplying organizational and job context.