Results for “hrfv_job_applications”

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

Overview

HRFV_JOB_APPLICATIONS is a business view template owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered as part of the PER (Human Resources) product family. Its documented purpose is to serve as a template from which a flexfield view is generated. In practice, the view consolidates recruitment-oriented data — applications, vacancies, applicants, assignments, and their associated statuses — into a single queryable structure that supports reporting and integration against the Oracle Human Resources and iRecruitment data model.

Because the view encapsulates complex decoding logic around assignment and application statuses, it allows downstream reports, BI Publisher extracts, and custom interfaces to avoid re-implementing status-derivation rules. The presence of numerous PER_SYSTEM_STATUS references confirms that the view is heavily oriented toward exposing lifecycle states of applications and assignments in a business-friendly format.

Underlying Base Objects

The documented referenced base objects reveal a moderate-to-deep dependency on core HR tables and views. Key transactional sources include PER_APPLICATIONS (application records), PER_ALL_VACANCIES (vacancy definitions), PER_ALL_ASSIGNMENTS_F and PER_ASSIGNMENTS_F (assignment history), and PER_PERIODS_OF_SERVICE (period-of-service data). Status semantics are supplied by PER_ASSIGNMENT_STATUS_TYPES, PER_ASSIGNMENT_STATUS_TYPES_TL, PER_ASS_STATUS_TYPE_AMENDS, and PER_ASS_STATUS_TYPE_AMENDS_TL, which provide system status codes and translated user status labels.

Descriptive attributes are joined from HR_ALL_ORGANIZATION_UNITS_TL, HR_ALL_POSITIONS_F_TL, HR_LOCATIONS_ALL_TL, PER_GRADES_TL, PER_JOBS_TL, PAY_PEOPLE_GROUPS, and PER_PEOPLE_X. Recruiting activity context comes from PER_RECRUITMENT_ACTIVITIES. Several package references — HR_BIS, HR_GENERAL, HR_PERSON_NAME, HR_SECURITY, and HRI_OLTP_VIEW_ASG_STATUS_TYPES — indicate that the view draws on HR security and status-type logic at runtime, ensuring that row-level security and status derivation behave consistently with the rest of the HR product.

Key Columns

• BUSINESS_GROUP_NAME — the operating business group (BGRT.NAME) associated with the record.
• PERSON_NAME — the applicant's full name, resolved via the HR person-name logic.
• VACANCY_NAME — the vacancy against which the application was submitted.
• START_DATE / END_DATE — mapped from the application's DATE_RECEIVED and DATE_END, bounding the application window.
• CURRENT_EMPLOYER and PROJECTED_HIRE_DATE — applicant-supplied recruiting attributes.
• APPLICATION_STATUS — a derived column resolved from PER_SYSTEM_STATUS values such as TERM_APL and TERM_ASSIGN, combined with translated user status text. This is the column most closely tied to the searched term per_system_status.
• NEW_APPLICANTS — indicator set to 1 when the resolved status equals ACTIVE_APL.
• INTERVIEW1 / INTERVIEW2 — indicators for the INTERVIEW1 and INTERVIEW2 statuses.
• TERMINATIONS, OFFERS, ACCEPTS — indicator flags for TERM_APL, OFFER, and ACCEPTED statuses respectively.

The repeated use of DECODE and nested LEAST/TRUNC(SYSDATE) logic shows that status is not simply a stored lookup; it is computed from the intersection of assignment type (A or E), effective dates, and PER_SYSTEM_STATUS values.

Common Use Cases and Queries

Typical uses include recruitment pipeline reporting, applicant funnel analysis (new applicants vs. interviews vs. offers vs. accepts), and termination tracking. A representative query is:

SELECT business_group_name,
       person_name,
       vacancy_name,
       application_status,
       new_applicants,
       interview1,
       interview2,
       offers,
       accepts
FROM   apps.hrfv_job_applications;

To aggregate pipeline volume by status:

SELECT application_status,
       COUNT(*) total
FROM   apps.hrfv_job_applications
GROUP  BY application_status
ORDER  BY total DESC;

Because HR_SECURITY and HR_BIS participate in the definition, queries executed under a restricted HR responsibility return only rows the user is authorized to see. For integration or extraction, the view is best consumed as-is rather than reconstructing its DECODE logic, since the status derivation rules are non-trivial and version-specific across 12.1.1 and 12.2.2.