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.
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’
