Search Results vacancy_status




Overview
Explore in AI Chat

Overview

APPS.EDWBV_HR_RQVC_VACANCY_LCV is a Business Intelligence (BI) view shipped with the Oracle E-Business Suite HRMS/ODI-ETRM data model. The name follows the Oracle BI Applications convention, where EDW denotes the Enterprise Data Warehouse layer, BV denotes a business view, and HR_RQVC_VACANCY_LCV identifies the subject area "Recruitment and Vacancy." The view presents vacancy-level detail drawn from Oracle Human Resources (PER) tables, combining each vacancy with its parent requisition, the local instance identifier, and its owning business group organization. It is designed as a source for extraction into the HR data warehouse and for downstream requisition/vacancy analytics, rather than as an operational transactional object.

The suffix _LCV indicates a "local" or source-oriented view used within the ETRM/BI Applications job stream to feed full and incremental warehouse loads. Users searching for vacancy_status will find this view directly relevant because it exposes the raw code column and its decoded display value.

Underlying Base Objects

The documented view definition references the following objects in its FROM clause and join conditions:

  • PER_ALL_VACANCIES (alias VCS) — the primary source of vacancy records, including status, number of openings, budget measurement attributes, name, dates, and owning business group.
  • EDW_HR_RQVC_REQUISTN_LCV (alias REQ) — a companion BI view that supplies the parent requisition, providing requisition_pk and requisition_id.
  • EDW_LOCAL_INSTANCE (alias INST) — supplies the instance_code, which identifies the source instance so that records from multiple installations can be distinguished.
  • HR_ALL_ORGANIZATION_UNITS (alias BGR) — the organization unit that represents the business group owning each vacancy; used to enrich the vacancy name.

The join conditions link a vacancy to its requisition (vcs.requisition_id = req.requisition_id) and to its business group (vcs.business_group_id = bgr.organization_id), with the local instance cross-joined to stamp the source system.

Key Columns

The view exposes both technical keys and descriptive attributes. The most important are:

  • vacancy_pk — a composite surrogate key formed as requisition_pk || '-' || vacancy_id || '-' || instance_code, uniquely identifying a vacancy across instances.
  • requisition_fk / requisition_id — the parent requisition reference, linking the vacancy to its requisition record.
  • instance — the source instance code from EDW_LOCAL_INSTANCE.
  • vacancy_status_code — the raw lookup code from per_all_vacancies.status; this is the column most relevant to a search on "vacancy_status."
  • vacancy_status — the decoded, display-ready status produced by hr_general.decode_lookup('VACANCY_STATUS', vcs.status), returning values such as Open, Closed, or Cancelled.
  • number_of_openings — the count of positions associated with the vacancy.
  • budget_msrmnt_type_code / budget_msrmnt_type — the budget measurement type code and its decoded value (via the BUDGET_MEASUREMENT_TYPE lookup).
  • budget_msrmnt_value — the numeric budget measurement value for the vacancy.
  • name / vacancy_dp — a display name composed as vacancy_name(business_group, instance_code).
  • business_group_id — the owning business group organization identifier.
  • vacancy_start_date / vacancy_end_date — the effective date range (from date_from and date_to).
  • creation_date / last_update_date — audit columns; last_update_date is coalesced to 01-JAN-2000 when null, a common BI extraction convention.

The literal '_DF:PER:PER_VACANCIES:vcs' embedded in the SELECT is a data-lineage tag identifying the originating PER entity, which is useful when tracing warehouse records back to source.

Common Use Cases and Queries

Typical scenarios include populating vacancy dimension and fact tables in the HR warehouse, reporting on open positions by business group, and auditing vacancy status distribution. The following query retrieves vacancy status together with requisition and business group context:

  • SELECT vacancy_pk, requisition_fk, instance, vacancy_status_code, vacancy_status, number_of_openings, name, vacancy_start_date, vacancy_end_date FROM apps.edwbv_hr_rqvc_vacancy_lcv;
  • Filtering on status: ... WHERE vacancy_status_code = 'OPEN'; or using the decoded value WHERE vacancy_status = 'Open';
  • Grouping for headcount planning: SELECT vacancy_status, SUM(number_of_openings) FROM apps.edwbv_hr_rqvc_vacancy_lcv GROUP BY vacancy_status;
  • Instance-scoped extraction: ... WHERE instance = :instance_code to isolate a single source installation.

Because the decode functions rely on lookup types maintained in the HR lookup tables, the vacancy_status column reflects whatever display values are configured for the VACANCY_STATUS lookup in the target instance. When precise API-level status handling or additional vacancy attributes are required beyond those surfaced here, the underlying PER_ALL_VACANCIES table should be joined directly.