User Hook
Overview
1. API User Hooks allow users to extend the business logic of the standard business rules that are executed by APIs. This is done by allowing custom procedures to be called at specific points in the standard APIs. For instance, we are implement User Hooks for extending the validation of data beyond what the standard system has provided.
Steps for Implementing User Hooks
1. Choose the API you wish to hook some extra logic to.
2. Write the PL/SQL procedure that you wish to be called by the hook.
3. Register or associate the procedure you have written with one or more specific user hooks.
4. Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook specified in 3.
Example
1. If validations need to be done on Person Extra Information when the information is created then we need to check the availability of module_name called as CREATE%PERSON%EXTRA%INFO%
OR we need to guess related API in all_objects
SELECT *
FROM hr_api_modules
WHERE api_module_type = 'BP'
AND module_name LIKE 'CREATE%PERSON%EXTRA%INFO%'
OR
select * from all_objects where object_name like '%PERSON%EXTRA%API'
2. If module exist we need to find out it’s module_id and related hook_ids
SELECT ahk.api_hook_id,ahm.api_module_id, ahk.hook_package, ahk.hook_procedure, ahk.api_hook_type, ahm.api_module_type
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name = 'CREATE_PERSON_EXTRA_INFO'
AND ahm.api_module_type in('BP')
AND ahk.api_hook_type in('AP','BP')
AND ahk.api_module_id = ahm.api_module_id
Query Result :
|
API_HOOK_ID
|
API_MODULE_ID
|
HOOK_PACKAGE
|
HOOK_PROCEDURE
|
API_HOOK_TYPE
|
API_MODULE_TYPE
|
|
2759
|
1226
|
HR_PERSON_EXTRA_INFO_BK1
|
CREATE_PERSON_EXTRA_INFO_A
|
AP
|
BP
|
|
2758
|
1226
|
HR_PERSON_EXTRA_INFO_BK1
|
CREATE_PERSON_EXTRA_INFO_B
|
BP
|
BP
|
Here BP – Before Process and AP – After Process
The 2 Business Process hook and 3 Row Handler Hooks available:
Before Process – These hooks execute logic before the main API logic. The majority of validation will not have taken place. No database changes will have been made.
After Process – These hooks will execute after the main API validation has completed and database changes made. If the main validation failed then the user hook will not be called.
The 3 types of Row Handler (RH) hook available are:
§ After Insert
§ After Update
§ After Delete
3. Usually we use After Process. (say : CREATE_PERSON_EXTRA_INFO_A)
4. Create Custom Package and procedure code
CREATE OR REPLACE PACKAGE ak_user_hook_leave_return_pkg
IS
PROCEDURE Air_ticket_request (p_person_id IN NUMBER,
p_information_type IN VARCHAR2,
p_pei_information1 IN VARCHAR2);
END ak_user_hook_leave_return_pkg;
Note : While creating Custom procedure pass the parameters which are present in standard procedure
Parameters in procedure Air_ticket_request should match with parameters in CREATE_PERSON_EXTRA_INFO_A.
Due to this standard procedure CREATE_PERSON_EXTRA_INFO_A passes values like p_person_id, p_information_type, p_pei_information1 dynamically to our custom procedure Air_ticket_request.
5. Registering the 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 (sysdate),
p_api_hook_id => 2759, ---from point 2
p_api_hook_call_type => 'PP',
p_sequence => 3000,
p_enabled_flag => 'Y',
p_call_package => 'XX_USER_HOOK_PKG', ---our custom package
p_call_procedure => ‘XXAIR_TICKET_REQ', ---our custom procedure
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
END;
Delete User Hook created (Use when required)
DECLARE
l_api_hook_call_id NUMBER := 1210; --pass appropriate value
l_object_version_number NUMBER := 27; --pass appropriate value
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;
6. Check the Table : hr_api_hook_calls
If custom code is properly hooked with standard code then one record will be created
SELECT * FROM hr_api_hook_calls WHERE TRUNC (SYSDATE) = TRUNC (creation_date)
7. Running the Pre-Processor (Mostly done by DBA)
Run following is the command in Putty
(We need to find location of file : hrahkone.sql)
> cd $PER_TOP/admin/sql or > cd $PER_TOP/ patch/115/sql or any other suggested by DBA
(Open sqlplus)
> sqlplus username/password
(Run the file hrahkone.sql)
> @hrahkone.sql
It will ask for api_module_id which we found at point 2
> Enter value for api_module_id: 1226
If all works fine it will show message as:
--------------------------------------------------
PL/SQL procedure successfully completed.
CREATE_PERSON_EXTRA_INFO(Business Process API) successful.
8. Check the Table again : hr_api_hook_calls
If Pre-Processor is successful STATUS will be ‘V’ else it will be ‘I or N’
