User Hook

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’