How to Register/Delete Procedure in User Hook
1) Create API Hook Call (Register Procedure in User Hook)
· Package Name: hr_api_hook_call_api.create_api_hook_call
declare
l_api_hook_call_id number;
l_object_version_number number;
begin
hr_api_hook_call_api.create_api_hook_call
(p_validate => false,
p_effective_date => to_date('01-JAN-1950,'DD-MON-YYYY'),
p_api_hook_id => 63,
p_api_hook_call_type => 'PP',
p_sequence => 3000,
p_enabled_flag => 'Y',
p_call_package => 'SCOOP_NATIONALITY_CHECK',
p_call_procedure => 'POLISH_NAME_CHECK',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
end;
i. The hook mechanism only supports calls to package procedures currently so api_hook_call_type must be PP.
ii. Sequence numbers > 2000 are recommended, as sequences < than 2000 are reserved for Oracle seeded logic which needs to be processed first. Please note that you can add any number of hooks to the same API, and order them using the sequence.
2) Delete API Hook Call (Delete Registered Procedure in User Hook)
· Package Name: hr_api_hook_call_api.delete_api_hook_call
declare
l_api_hook_call_id number := 2;
l_object_version_number number := 3;
begin
hr_api_hook_call_api.delete_api_hook_call
(p_validate => false,
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
end;
Demo on how to register Procedure in User Hook:
--Step 1
select * from hr_api_modules where module_name ='CREATE_PERSON_ABSENCE'
-- Step 2
select ahk.api_hook_id,ahk.api_hook_type,
ahk.hook_package,
ahk.hook_procedure from hr_api_hooks ahk where
api_module_id = 1739
-- Step 3 -- Get All registered Packages and Prcoedures
select * from hr_api_hook_calls where api_hook_id = 3868 -- After Proccess
-- Step 3 – Complete Query
select ahk.api_hook_id,ahk.api_hook_type,
ahk.hook_package,
ahk.hook_procedure
from hr_api_hooks ahk,
hr_api_modules ahm
where ahm.module_name='CREATE_PERSON_ABSENCE'
and ahk.api_module_id=ahm.api_module_id
-- How to Register Procedure to user Hook
declare
l_api_hook_call_id number;
l_object_version_number number;
begin
hr_api_hook_call_api.create_api_hook_call
(p_validate => false,
p_effective_date => to_date('01-JAN-1950','DD-MON-YYYY'),
p_api_hook_id => 3868,-- API HOOK ID
p_api_hook_call_type => 'PP',
p_sequence => 3100,-- SEQ NO
p_enabled_flag => 'Y',
p_call_package => 'KFSH_WF_NOTIFY_PKG', -- Package Name
p_call_procedure => 'KFSH_NOTIFY_UNPAID_LEAVE_P', -- Procedure Name
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
dbms_output.put_line('Procedure Successfully Registered');
exception
when others then
dbms_output.put_line(sqlerrm);
end;
-- Run the Pre Processor ----
DECLARE
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module(1739); -- Module ID
dbms_output.put_line('Pre-Preocessor Run Successfully');
exception
when others then
dbms_output.put_line(sqlerrm);
END;
