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
/* Formatted on 4/13/2013 4:30:16 PM (QP5
v5.163.1008.3004) */
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;
