Search Results per_all_vacancies

Overview

The PER_ALL_VACANCIES table is a core data object within the Oracle E-Business Suite Human Resources (PER) module, specifically for Oracle EBS releases 12.1.1 and 12.2.2. It serves as the primary repository for vacancy records, which represent specific, open positions to be filled within an organization. Each vacancy is intrinsically linked to a specific requisition, as indicated by its description, "Vacancies within a specific requisition." This table is central to the recruitment lifecycle, enabling the tracking of open positions, their associated details, and their integration with other HR processes such as assignments and offers.

Key Information Stored

The table stores comprehensive details for each vacancy. Its primary key is VACANCY_ID, ensuring each record is uniquely identifiable. A unique key constraint (PER_VACANCIES_UK2) also exists on the combination of REQUISITION_ID and NAME, enforcing uniqueness of vacancy names within a requisition. Key foreign key columns define the vacancy's context within the HR organizational structure and job framework. These include BUSINESS_GROUP_ID, ORGANIZATION_ID, POSITION_ID, JOB_ID, and GRADE_ID. Additional critical columns link the vacancy to a specific LOCATION_ID and PEOPLE_GROUP_ID. The REQUISITION_ID column is mandatory, establishing the foundational link to the PER_REQUISITIONS table from which the vacancy is created.

Common Use Cases and Queries

This table is pivotal for recruitment reporting and operational processes. Common use cases include generating open vacancy reports, tracking recruitment pipeline metrics, and providing data for integration with Oracle iRecruitment. A typical query might join PER_ALL_VACANCIES with related tables to list all active vacancies with their organizational and job details:

• SELECT pv.NAME, pv.VACANCY_ID, pr.REQUISITION_CODE, hou.NAME ORG_NAME, pj.NAME JOB_NAME FROM HR.PER_ALL_VACANCIES pv JOIN HR.PER_REQUISITIONS pr ON pv.REQUISITION_ID = pr.REQUISITION_ID JOIN HR.HR_ALL_ORGANIZATION_UNITS hou ON pv.ORGANIZATION_ID = hou.ORGANIZATION_ID JOIN HR.PER_JOBS pj ON pv.JOB_ID = pj.JOB_ID WHERE pv.DATE_TO IS NULL;

Another critical use case is data validation, ensuring vacancies are correctly linked to valid positions, jobs, and organizations before mass assignment or recruitment activities proceed.

Related Objects

PER_ALL_VACANCIES has extensive relationships with other HR and iRecruitment objects, as shown in its foreign key constraints. It is a parent table to several recruitment-specific entities: IRC_AGENCY_VACANCIES, IRC_OFFERS, IRC_REC_TEAM_MEMBERS, and IRC_VACANCY_CONSIDERATIONS. Crucially, it is referenced by the core HR assignment table (PER_ALL_ASSIGNMENTS_F.VACANCY_ID), linking a filled vacancy to an employee's assignment record. It is also referenced by PER_RECRUITMENT_ACTIVITY_FOR for tracking activities. Primary parent tables include PER_REQUISITIONS (source), PER_ALL_POSITIONS, PER_JOBS, PER_GRADES, HR_ALL_ORGANIZATION_UNITS (twice, for business group and organization), HR_LOCATIONS_ALL, and PAY_PEOPLE_GROUPS.