How to get iRecruitment Vacancy details in Oracle HRMS before Vacancy is approved.

Generally details of Vacancies created in Oracle iRecruitment are store in ‘PER_ALL_VACANCIES’ table. But you cant see the vacancy details in ‘PER_ALL_VACANCIES’ table if the vacancy is NOT approved.

Where does Vacancy details get stored  before Approval/Rejection

As soon as vacancy is created the vacancy related data goes and resides in some temporary tables. This data is retained within the temporary tables until the vacancy is either Rejected or Approved. These temporary table names begin with name ‘HR_API%’.

Not only vacancies but also other Self Service HRMS details are stored in temporary table before completion of it entire transaction, i.e either Approved or Rejected.

Coming to iRecruitment vacancy, the data entered by the user is either just stored within one table HR_API_TRANSACTIONS or within HR_API_TRANSACTION_VALUES. The entire data entered by the user is captured in a CLOB column of HR_API_TRANSACTIONS. The data is captured into this CLOB Column because of some performance reasons. ‘transaction_document’ is the name of the CLOB column in HR_API_TRANSACTIONS, this CLOB column contains XML contents.

How does Workflow Notification Fetch Data?

Approval Management Engine(AME) of Oracle HRMS extracts data from this CLOB column through an SQL Query and shows it as notification to the Approver who need to Approve the Vacancy.

You can identifiy the ‘transaction_document’ column of HR_API_TRANSACTIONS table uniquely by two parameters transaction_ref_table and ‘transaction_ref_id’.
where transaction_ref_table is always ‘PER_ALL_VACANCIES’
and transaction_ref_id is numeric part of Vacancy name. suppose if Vacancy name is ‘IRC1234’ then transaction_ref_id is 1234.
In short you can extract your vacancy related information from HR_API_TRANSACTIONS table with the following query.

SELECT *
FROM HR_API_TRANSACTIONS
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
AND transaction_ref_id      = 1234;

please note that this query may result in a warning as ‘Data type not supported’ if you are using SQL IDE TOAD 7.0v as the query results in showing some CLOB data.

suppose if you want vacancy_id, business_group_id, number_of_openings, manager_id of Vacancy IRC1234 then you need to execute the following query.

SELECT XMLTYPE (transaction_document).EXTRACT
('//PerAllVacanciesEORow/VacancyId/text()').getstringval
() vacancy_id,
XMLTYPE (transaction_document).EXTRACT
('//PerAllVacanciesEORow/BusinessGroupId/text()').getstringval
() business_group_id,
XMLTYPE (transaction_document).EXTRACT
('//PerAllVacanciesEORow/NumberOfOpenings/text()'
).getstringval () number_of_openings,
XMLTYPE (transaction_document).EXTRACT
('//PerAllVacanciesEORow/ManagerId/text()').getstringval
() manager_id
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES' AND transaction_ref_id = 1234;

as said earlier transaction_ref_id is the numeric part of Vacancy name.
XMLTYPE ().Extract is a function to extract data from XML file and ‘PerAllVacanciesEORow’ is the XML tag node.

Vacancy WorkFlow

To see any workflow you need WorkFlow type and ItemKey, you can fetch them from HR_API_TRANSACTIONS table by running the following query

SELECT item_type, item_key
  FROM hr_api_transactions
 WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
   AND transaction_ref_id = 9204;

please leave comments if you have any questions.