Oracle has provided user hooks to implement custom logic or validation on standard processes.
For example:
- Creating a element entry when creating an absence
- validating the DFF segments in Absence before creating a absence.
- Validation on creating EITs, Element entries, absence, termination of employee etc
Now lets go through the steps to attach a user hook. I am assuming the user hook for create absence process.
Step 1 :
Get the Module id from table HR_API_MODULES. In my case the module name is like 'CREATE%ABSENCE%'. Hence I query for the module using the below query.
SELECT * FROM hr_api_modules WHERE module_name LIKE 'CREATE%ABSENCE%';
I get the api_module_id as 1731.
---------------------------------------------------------------------------
Step 2:
Next I query for hook id in table hr_api_hooks for after process. Note that 'AP' means After Process hook and 'BP' isBefore Process hook.
SELECT * FROM hr_api_hooks WHERE api_module_id = 1731;
I get the api_hook_id as 3840
--------------------------------------------------------------------------
Step 3:
If you know the Module name , hook package name and hook procedure , you can use the script below .
Script for attaching the hook:
Example 1:
set feedback off
set serverout on size 10000
Declare
CURSOR c1_get_id IS
SELECT md.module_name mod_nm,
hk.api_hook_id hk_id,
md.api_module_id mod_id,
hk.api_hook_type hk_type
FROM hr_api_hooks hk, hr_api_modules md
WHERE hk.api_module_id = md.api_module_id
AND md.module_name IN ('CREATE_PERSON_ABSENCE'
, 'UPDATE_PERSON_ABSENCE'
)
AND md.api_module_type = 'BP'
AND hk.api_hook_type in ( 'AP','BP')
;
--
CURSOR c2_get_seq_no IS
SELECT MAX(hc.SEQUENCE)
FROM hr_api_hook_calls hc
;
--
CURSOR c_chk_call( hk_id IN NUMBER) IS
SELECT hc.api_hook_call_id,
hc.SEQUENCE, hc.enabled_flag, hc.call_package,
hc.call_procedure, hc.object_version_number
FROM hr_api_hook_calls hc
WHERE hc.api_hook_id = hk_id
AND hc.call_package='XXARJPER_NEGATIVE_BALANCE_PKG'
AND hc.enabled_flag = 'Y'
;
--
l_ai_bp_id NUMBER;
l_au_bp_id NUMBER;
l_ai_ap_id NUMBER;
l_au_ap_id NUMBER;
l_hk_type VARCHAR2(10);
l_hk_call_seq NUMBER;
l_mod_id_ai_bp NUMBER;
l_mod_id_au_bp NUMBER;
l_mod_id_ai_ap NUMBER;
l_mod_id_au_ap NUMBER;
l_api_hook_call_id NUMBER;
l_object_version_number NUMBER;
l_cur_hkcall_id NUMBER;
l_cur_seqn NUMBER;
l_cur_flag VARCHAR2(1);
l_cur_pack VARCHAR2(30);
l_cur_proc VARCHAR2(30);
l_cur_num NUMBER;
--
BEGIN
--
FOR i IN c1_get_id LOOP
IF i.mod_nm = 'CREATE_PERSON_ABSENCE' and i.hk_type='BP' THEN
l_ai_bp_id := i.hk_id;
l_mod_id_ai_bp := i.mod_id;
ELSIF i.mod_nm = 'UPDATE_PERSON_ABSENCE' and i.hk_type='BP' THEN
l_au_bp_id := i.hk_id;
l_mod_id_au_bp := i.mod_id;
ELSIF i.mod_nm = 'CREATE_PERSON_ABSENCE' and i.hk_type='AP' THEN
l_ai_ap_id := i.hk_id;
l_mod_id_ai_ap := i.mod_id;
ELSIF i.mod_nm = 'UPDATE_PERSON_ABSENCE' and i.hk_type='AP' THEN
l_au_ap_id := i.hk_id;
l_mod_id_au_ap := i.mod_id;
END IF;
END LOOP;
--
OPEN c2_get_seq_no;
FETCH c2_get_seq_no INTO l_hk_call_seq;
CLOSE c2_get_seq_no;
--
IF l_hk_call_seq > 2000 THEN
l_hk_call_seq := (l_hk_call_seq + 1);
ELSE
l_hk_call_seq := 2001;
END IF;
-- For create absence before process
OPEN c_chk_call(l_ai_bp_id);
FETCH c_chk_call INTO
l_cur_hkcall_id,l_cur_seqn,l_cur_flag,l_cur_pack,l_cur_proc,l_cur_num;
CLOSE c_chk_call;
--
IF l_cur_hkcall_id IS NOT NULL THEN
Hr_Api_Hook_Call_Api.delete_api_hook_call
(p_api_hook_call_id => l_cur_hkcall_id,
p_object_version_number => l_cur_num
);
END IF;
--
l_api_hook_call_id := NULL;
--
Hr_Api_Hook_Call_Api.create_api_hook_call
(p_effective_date => TRUNC(SYSDATE),
p_api_hook_id => l_ai_bp_id,
p_api_hook_call_type => 'PP',
p_sequence => l_hk_call_seq,
p_enabled_flag => 'Y' ,
p_call_package => 'XXARJPER_NEGATIVE_BALANCE_PKG',
p_call_procedure => 'SET_PROF_OPT_CREATE_PRC',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number) ;
--
Hr_Api_User_Hooks_Utility.create_hooks_one_module(l_mod_id_ai_bp);
Hr_Api_User_Hooks_Utility.write_one_errors_report(l_mod_id_ai_bp);
--
l_cur_hkcall_id := NULL;
l_cur_seqn := NULL;
l_cur_flag := NULL;
l_cur_pack := NULL;
l_cur_proc := NULL;
l_cur_num := NULL;
-- For update absence before process
OPEN c_chk_call(l_au_bp_id);
FETCH c_chk_call INTO
l_cur_hkcall_id,l_cur_seqn,l_cur_flag,l_cur_pack,l_cur_proc,l_cur_num;
CLOSE c_chk_call;
--
IF l_cur_hkcall_id IS NOT NULL THEN
Hr_Api_Hook_Call_Api.delete_api_hook_call
(p_api_hook_call_id => l_cur_hkcall_id,
p_object_version_number => l_cur_num
);
END IF;
--
l_api_hook_call_id := NULL;
--
Hr_Api_Hook_Call_Api.create_api_hook_call
(p_effective_date => TRUNC(SYSDATE),
p_api_hook_id => l_au_bp_id,
p_api_hook_call_type => 'PP',
p_sequence => (l_hk_call_seq + 1),
p_enabled_flag => 'Y' ,
p_call_package => 'XXARJPER_NEGATIVE_BALANCE_PKG',
p_call_procedure => 'SET_PROF_OPT_UPDATE_PRC',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number) ;
--
Hr_Api_User_Hooks_Utility.create_hooks_one_module(l_mod_id_au_bp);
Hr_Api_User_Hooks_Utility.write_one_errors_report(l_mod_id_au_bp);
--
l_cur_hkcall_id := NULL;
l_cur_seqn := NULL;
l_cur_flag := NULL;
l_cur_pack := NULL;
l_cur_proc := NULL;
l_cur_num := NULL;
-- For create absence after process
OPEN c_chk_call(l_ai_ap_id);
FETCH c_chk_call INTO
l_cur_hkcall_id,l_cur_seqn,l_cur_flag,l_cur_pack,l_cur_proc,l_cur_num;
CLOSE c_chk_call;
--
IF l_cur_hkcall_id IS NOT NULL THEN
Hr_Api_Hook_Call_Api.delete_api_hook_call
(p_api_hook_call_id => l_cur_hkcall_id,
p_object_version_number => l_cur_num
);
END IF;
--
l_api_hook_call_id := NULL;
--
Hr_Api_Hook_Call_Api.create_api_hook_call
(p_effective_date => TRUNC(SYSDATE),
p_api_hook_id => l_ai_ap_id,
p_api_hook_call_type => 'PP',
p_sequence => (l_hk_call_seq + 2),
p_enabled_flag => 'Y' ,
p_call_package => 'XXARJPER_NEGATIVE_BALANCE_PKG',
p_call_procedure => 'RESTRICT_NEG_BAL_CREATE_PRC',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number) ;
--
Hr_Api_User_Hooks_Utility.create_hooks_one_module(l_mod_id_ai_ap);
Hr_Api_User_Hooks_Utility.write_one_errors_report(l_mod_id_ai_ap);
--
l_cur_hkcall_id := NULL;
l_cur_seqn := NULL;
l_cur_flag := NULL;
l_cur_pack := NULL;
l_cur_proc := NULL;
l_cur_num := NULL;
-- For update absence after process
OPEN c_chk_call(l_au_ap_id);
FETCH c_chk_call INTO
l_cur_hkcall_id,l_cur_seqn,l_cur_flag,l_cur_pack,l_cur_proc,l_cur_num;
CLOSE c_chk_call;
--
IF l_cur_hkcall_id IS NOT NULL THEN
Hr_Api_Hook_Call_Api.delete_api_hook_call
(p_api_hook_call_id => l_cur_hkcall_id,
p_object_version_number => l_cur_num
);
END IF;
--
l_api_hook_call_id := NULL;
--
Hr_Api_Hook_Call_Api.create_api_hook_call
(p_effective_date => TRUNC(SYSDATE),
p_api_hook_id => l_au_ap_id,
p_api_hook_call_type => 'PP',
p_sequence => (l_hk_call_seq + 3),
p_enabled_flag => 'Y' ,
p_call_package => 'XXARJPER_NEGATIVE_BALANCE_PKG',
p_call_procedure => 'RESTRICT_NEG_BAL_UPDATE_PRC',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number) ;
--
Hr_Api_User_Hooks_Utility.create_hooks_one_module(l_mod_id_au_ap);
Hr_Api_User_Hooks_Utility.write_one_errors_report(l_mod_id_au_ap);
--
--
COMMIT;
--
END;
--------------------
Example 2:-
-set serveroutput on size 1000000
DECLARE
--
ln_api_hook_call_id number;
ln_object_version_number number;
ln_api_hook_id number;
--
BEGIN
--
select ahk.api_hook_id
into ln_api_hook_id
from hr_api_hooks ahk, hr_api_modules ahm
where ahm.module_name = 'CREATE_PERSON_ABSENCE'
and ahm.api_module_type = 'BP'
and ahk.hook_package = 'HR_PERSON_ABSENCE_BK1'
and ahk.hook_procedure = 'CREATE_PERSON_ABSENCE_A'
and ahk.api_hook_type = 'AP' -- After Process
and ahk.api_module_id = ahm.api_module_id;
--
-- insert a row into HR_API_HOOK_CALLS
--
hr_api_hook_call_api.create_api_hook_call
(p_effective_date => to_date('14-MAR-2014','DD-MON-YYYY')
,p_api_hook_id => ln_api_hook_id
,p_api_hook_call_type => 'PP'
,p_sequence => 3029
,p_enabled_flag => 'Y'
,p_call_package => 'XXMUD_DISCIPLINARY_ACTION_PKG'--Custom Hook PKG
,p_call_procedure => 'CREATE_UNAUTHORIZED_ABS_ENTRY'--Procedure for creating entries
,p_api_hook_call_id => ln_api_hook_call_id
,p_object_version_number => ln_object_version_number);
--
DBMS_OUTPUT.PUT_LINE('Registered HOOK...'|| ln_api_hook_call_id );
--
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('Error: '||SQLERRM,1,255));
END;
-------------------------------------------------------
Step 4:
Next step is to run the pre-processor to the hook. Without running the pre-processor the user hook will not work.
DECLARE
l_module_id NUMBER; --Pass the module id
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module (1282);
END;
COMMIT;
--------------------------------------------------------
Step 5:
Next step is to verify if hook is registered.
SELECT * FROM hr_api_hook_calls
WHERE call_package = 'XXMUD_DISCIPLINARY_ACTION_PKG';
If STATUS column is 'V' (Stands for Valid) and ENABLED_FLAG = 'Y' then you have successfully registered the user hook.
----------------------------------------------------------------------------
Deleting User Hook:
DECLARE
ln_object_version_number NUMBER;
ln_api_hook_call_id NUMBER;
BEGIN
-- Get the api_hook_call_id and object_version_number
SELECT api_hook_call_id, object_version_number
INTO ln_api_hook_call_id,ln_object_version_number
FROM hr_api_hook_calls
WHERE hook_call_package = 'XXMUD_DISCIPLINARY_ACTION_PKG'
AND hook_procedure = 'CREATE_UNAUTHORIZED_ABS_ENTRY';
--API to delete the user hook
hr_api_hook_call_api.delete_api_hook_call
(p_api_hook_call_id => ln_api_hook_call_id, -- API_HOOK_CALL_ID
p_object_version_number => ln_object_version_number ); -- Object_version_number
DBMS_OUTPUT.PUT_LINE('DELETED HOOK...');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('Error: '||SQLERRM,1,255));
END;
