Search Results irc_agency_vacancies_u1

Overview

The IRC_AGENCY_VACANCIES table is a core data object within the Oracle E-Business Suite Human Resources (PER) module, specifically supporting the iRecruitment functionality. It serves as a junction or association table that establishes and manages the relationship between external recruitment agencies and internal job vacancies. Its primary role is to enable the tracking of which staffing agencies have been engaged or are authorized to submit candidates for specific open positions within the organization. This table is critical for managing the procurement and vendor relationship aspects of the recruitment process, linking the HR vacancy data with the purchasing supplier data.

Key Information Stored

The table's structure is designed to store the essential foreign key relationships that define an agency-vacancy assignment. The primary data points stored are the unique identifiers that link to other master tables. The key columns include AGENCY_VACANCY_ID, which serves as the primary surrogate key for each record. The AGENCY_ID column holds a foreign key reference to a vendor (recruitment agency) in the purchasing module. The VACANCY_ID column holds a foreign key reference to a specific job vacancy defined within the HR system. The combination of AGENCY_ID and VACANCY_ID is enforced as a unique constraint (IRC_AGENCY_VACANCIES_U1), preventing duplicate agency assignments for the same vacancy.

Common Use Cases and Queries

This table is central to reporting and operational processes involving agency-sourced recruitment. Common use cases include generating a list of all agencies assigned to a particular vacancy for vendor management, auditing agency submissions per vacancy, and running reports on recruitment spend by agency. A typical query would join this table to vendor and vacancy details to produce a meaningful report. For example, to list all vacancies and their associated agencies, a SQL pattern would be:

• SELECT pov.vendor_name, pav.name vacancy_name
• FROM hr.irc_agency_vacancies iav,
• po.po_vendors pov,
• per.per_all_vacancies pav
• WHERE iav.agency_id = pov.vendor_id
• AND iav.vacancy_id = pav.vacancy_id;

Another common scenario is checking for existing agency assignments before creating a new one to avoid violating the unique constraint.

Related Objects

The IRC_AGENCY_VACANCIES table has defined foreign key relationships with two primary master tables, as documented in the ETRM metadata. These relationships are fundamental to its purpose:

• PO_VENDORS: The table is linked via the AGENCY_ID column to PO_VENDORS.VENDOR_ID. This relationship associates a recruitment vacancy with a specific supplier (agency) registered in the Purchasing module.
• PER_ALL_VACANCIES: The table is linked via the VACANCY_ID column to PER_ALL_VACANCIES.VACANCY_ID. This relationship ties the agency assignment to the actual job opening defined within Human Resources.

These relationships ensure data integrity, meaning a record in IRC_AGENCY_VACANCIES cannot exist without a valid corresponding vendor and vacancy.