What are HR API Hooks ?
Hooks enable us to extend the HR APIs’ capabilities to add additional custom features and validations. Hooks are easier to implement and can be used wherever HR APIs are called, be it a custom interface script or a seeded Self-Service request, if APIs are called then your custom validation will be invoked.
HR APIs are some times life saver, where-in the business wishes to add custom validations on a form or a self service page, rather than enhancing it, you just add your validation and you are good to go.
How to use HR API Hooks?
Tested In: Oracle EBS 12.1.1, 12.1.3, 12.2.6
We will take an example to create user hook on HR_EMPLOYEE_API. The sample business case is, I should not be able to hire anyone who is single.
1 .Get the Module ID and Hook ID
Query to get the API_MODULE_ID of the API we will be using, in my case HR_EMPLOYEE_API:
SELECT *
FROM HR_API_MODULES
WHERE module_package = 'HR_EMPLOYEE_API'
AND module_name = 'CREATE_EMPLOYEE' ;
Get the API_HOOK_ID for the module ID(1195), in my case BP – Before Process
SELECT API_HOOK_ID -- 2690
FROM HR_API_HOOKS
WHERE api_module_id = 1195 --Module ID from the previous query
AND API_HOOK_TYPE = 'BP';
2. Create your custom package
CREATE OR REPLACE PACKAGE XXHR_CUSTOM_HOOKS_PKG
AS
-- Make sure to use only those parameters available in the API are used
PROCEDURE validate_status(p_marital_status in varchar2 default null);
--
END XXHR_CUSTOM_HOOKS_PKG;
/
CREATE OR REPLACE PACKAGE BODY XXHR_CUSTOM_HOOKS_PKG
AS
PROCEDURE validate_status(p_marital_status in varchar2 default null)
IS
BEGIN
--If the marital status is single, throw an error
IF p_marital_status = 'S'
THEN
RAISE_APPLICATION_ERROR(-20006,'Custom Error: Single people cannot join our organization');
END IF;
END;
--
END XXHR_CUSTOM_HOOKS_PKG;
/
3. Create User Hook
User hooks can be created using the below script by passing the custom package we created the previous step:
SET SERVEROUTPUT ON; -- DECLARE l_api_hook_call_id NUMBER; l_object_version_number NUMBER; l_sequence NUMBER; BEGIN SELECT hr_api_hooks_s.NEXTVAL INTO l_sequence FROM DUAL; hr_api_hook_call_api.create_api_hook_call (p_validate => FALSE, p_effective_date => TO_DATE ('01-JAN-1952', 'DD-MON-YYYY'), p_api_hook_id => 2690, -- Retrieved Previously p_api_hook_call_type => 'PP', p_sequence => l_sequence, p_enabled_flag => 'Y', p_call_package => 'XXHR_CUSTOM_HOOKS_PKG', p_call_procedure => UPPER ('validate_status'), -- Custom procedure p_api_hook_call_id => l_api_hook_call_id, p_object_version_number => l_object_version_number ); DBMS_OUTPUT.put_line ('l_api_hook_call_id ' || l_api_hook_call_id); COMMIT; END; /
4. Register User Hook / Run Pre-Processor
DECLARE
l_api_module_id number;
BEGIN
l_api_module_id := 1195; -- Module ID Retrieved from previous query
--
-- Create all hook package body source code for one API module
--
hr_api_user_hooks_utility.create_hooks_one_module(l_api_module_id);
--
-- Build the report text
--
hr_api_user_hooks_utility.write_one_errors_report(l_api_module_id);
END;
/
Don’t forget to check the status of the hook call after running the pre-processor by using the below query:
SELECT status, encoded_error
FROM HR_API_HOOK_CALLS
where call_package = 'XXHR_CUSTOM_HOOKS_PKG'
and call_procedure = 'VALIDATE_STATUS';
What does the status mean?
N – New / Not Active
I – Invalid / Errored
V – Valid
If the pre-processor succeeded in registering your Hook call, then the status will be V if not then the status will be I. There is no direct way of finding the reason for the pre-processor to have failed. There is a script which can help you to find the error details which we will look into now. Run the below scripts in the same session where you ran pre-processor script to find the errors if any:
Note: This will only work if you have ran the hr_api_user_hooks_utility.write_one_errors_report(l_api_module_id); package call when you ran pre-processor as seen in the pre-processor script above.
/* Run the below script to get the error output */
set heading off
set feedback off
select text
from hr_api_user_hook_reports
where session_id = userenv('SESSIONID')
order by line;
/* Don't forget to clear the data from report tables by running the below script */
execute hr_api_user_hooks_utility.clear_hook_report;
5. Delete User Hook
If you wish to delete the user hook, you can use the below script by passing api_hook_call_id and object_version_number:
SET SERVEROUTPUT ON;
DECLARE
--
ln_object_version_number NUMBER;
--
BEGIN
/* You can get the api_hook_call_id by using the below query:
SELECT api_hook_call_id, object_version_number
FROM HR_API_HOOK_CALLS
where call_package = 'XXHR_CUSTOM_HOOKS_PKG'
and call_procedure = 'VALIDATE_STATUS';
*/
hr_api_hook_call_api.delete_api_hook_call (p_api_hook_call_id => 1112,
p_object_version_number => 1);
DBMS_OUTPUT.put_line ('DELETED HOOK...');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR ('Error: ' || SQLERRM, 1, 255));
END;
/
Important tables of Hooks:
SELECT * FROM HR_API_MODULES -- All Hook Modules can be found here
SELECT * FROM HR_API_HOOKS -- All hooks can be found here
SELECT * FROM HR_API_HOOK_CALLS -- All custom hook package calls can be found here
Caution:
1. API Hooks gets invoked if and only if HR API’s are called.
2. In quite a few cases HR API Hooks cannot be used where APIs aren’t called, few examples are:
-
- Certain forms like People Screen does not call API hence hooks will not work. (That’s where custom.pll or forms personalization comes into play)
- If you have configured a Special Information Type(SIT) with approvals, in Employee Self Service for employee’s to request for something, then don’t use hooks as API’s gets invoked only at the time of approval
