Search Results per_vacancies




Overview
Explore in AI Chat

Overview

PER_VACANCIES is an Oracle EBS Human Resources (PER) secure view owned by the APPS schema, delivered under Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes vacancy records for recruitment, headcount planning, and position management reporting, presenting a filtered projection of the underlying PER_ALL_VACANCIES table. A vacancy represents an authorized opening tied to a position, job, grade, or organization, against which requisitions and applicants are subsequently managed.

The view is defined as a "secure view based on the corresponding _ALL_ table," meaning it applies Oracle HRMS row-level security rather than exposing all rows unconditionally. Two predicates enforce this behaviour: HR_SECURITY.VIEW_ALL / HR_SECURITY.SHOW_RECORD restrict rows by the caller's security profile, and HR_GENERAL.GET_XBG_PROFILE / HR_GENERAL.GET_BUSINESS_GROUP_ID constrain rows to the current business group. Consequently, the same query returns different result sets for different users, which is significant for both reporting and integration design. For the user searching "requisition_id," this object is the appropriate source: REQUISITION_ID is a first-class column of the view, linking each vacancy to its associated requisition record.

Underlying Base Objects

ETRM 12.2.2 documents three referenced base objects for PER_VACANCIES:

  • PER_ALL_VACANCIES (SYNONYM) — the single table from which all columns are selected. PER_VACANCIES is a row-filtered, security-applied projection of this table; no joins, unions, or aggregations are applied.
  • HR_SECURITY (PACKAGE) — supplies VIEW_ALL and SHOW_RECORD, implementing row-level security by vacancy, organization, position, manager, and security method.
  • HR_GENERAL (PACKAGE) — supplies GET_XBG_PROFILE and GET_BUSINESS_GROUP_ID to enforce business group isolation across cross-business-group (XBG) configurations.

Because the view text is a straight column list over PER_ALL_VACANCIES with DECODE-based predicates, it is not inherently updatable in the same way as the base table. DML against vacancy data should be directed at PER_ALL_VACANCIES or through the supported HRMS APIs rather than through PER_VACANCIES.

Key Columns

Common Use Cases and Queries

Typical uses include reconciling vacancies to requisitions, reporting open headcount by organization or position, and joining vacancy data to applicant and requisition tables. Because the view enforces HR security, it is preferred over the base table in user-facing reports; the base table is preferred only in controlled, privileged ETL contexts.

List vacancies with their requisition reference:

  • SELECT vacancy_id, name, requisition_id, position_id, organization_id, status, number_of_openings FROM apps.per_vacancies WHERE status = 'OPEN';

Resolve a specific requisition back to its vacancy:

  • SELECT vacancy_id, name, requisition_id, date_from, date_to, recruiter_id FROM apps.per_vacancies WHERE requisition_id = :p_requisition_id;

Aggregate authorized openings by organization:

  • SELECT organization_id, COUNT(*) vacancy_count, SUM(number_of_openings) openings FROM apps.per_vacancies WHERE TRUNC(SYSDATE) BETWEEN date_from AND NVL(date_to, TRUNC(SYSDATE)) GROUP BY organization_id;

Note that results depend on the caller's security profile and business group; queries executed by a user without view-all privileges will return only those vacancies permitted by HR_SECURITY and HR_GENERAL.