How to use user hook on oracle applications (EBS)

Hello, Greetings From AppsLead. Today we are going to write about how to use user hooks on Oracle Applications. This topic is so useful for Technical and Functional consultants of HRMS Oracle Modules, In Appslead we always try to make every topic easy,  clear and straight-forward.

---

First lets start with what’s mean by user hook.

Concept:

These APIs are used to extend business logic and add more functionality or validation before or after some business events that are executed by APIs in Oracle HRMS,  When Standard processing reaches a user hook it stops and custom procedure having extra logic executed, after finishing processing the standard API resumes processing. Some business events:

• Validate Data is EIT before or after insertion either through self service or core HR.
• Validate Data is SIT before or after insertion either through self service or core HR.
• Maintain data held in extra customer specific tables (not part of Oracle Applications).
• Maintaining additional data in your own user defined tables.
• Detecting that a particular business event has occurred.
• Send alerts when particular events happen within Oracle Human Resources Management System(HRMS).

Notes: Not all Oracle HRMS forms use HRMS APIs, User hook can be implemented for only those forms which perform functions through API.

Types of user hooks:

Business Process:

• Before Process “BP”: The logic is executed by the hook before the main API, The main validation will not take place if this extra logic validation failed, No database changes will be made.
• After Process “AP”: The logic is executed by the hook after the main API validation passed and database changes made, If main validation failed the user hook will not be executed.

Row Handler:

• After Insert.
• After Update.
• After Delete.

---

Secondly, we are going to do an example of how to implement the user hook validation on Oracle Apps.

Implementation Steps:

Assume the logic needed to be built and upon it guess the name of the module that custom logic work on it, Decide whether to implement before or after main process, Say the extended logic  will be carried on Create Absence and after process. Step 1 Get the module id from HR_API_MODULES table

SELECT * 

FROM hr_api_modules 
WHERE module_name LIKE 'CREATE%ABSENCE%'

AND ahm.api_module_type = ‘BP’;

Step 2 Find hook id from HR_API_HOOKS, thereafter get name of hook package and hook procedure that will be used select * from hr_api_hooks ahk, hr_api_modules ahm where ahm.module_name = ‘CREATE_PERSON_ABSENCE’ and ahm.api_module_type = ‘BP’ and ahk.api_hook_type = ‘AP’ and ahk.api_module_id = ahm.api_module_id; Hook Type ‘AP’ After Process      |      ‘BP’ Before Process ‘AI’ After Insert    |    ‘AU’ After Update     |     ‘AD’ After Delete Step 3 Create custom package and custom procedure that will handle your logic and the extended business validation. Attention: The parameters of the custom procedure should match the parameters in the hook procedure. Step 4 Register the user hook by linking the custom package procedure to the hook package procedure DECLARE L_API_HOOK_ID NUMBER:= API Hook id from step 2; L_API_HOOK_CALL_ID NUMBER; L_OBJECT_VERSION_NUMBER NUMBER; L_SEQUENCE NUMBER; BEGIN SELECT HR_API_HOOKS_S.NEXTVAL INTO L_SEQUENCE FROM DUAL; HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL (P_VALIDATE => FALSE, P_EFFECTIVE_DATE => TO_DATE(sysdate, ‘DD-MON-YYYY’), P_API_HOOK_ID =>L_API_HOOK_ID NUMBER, –API Hook id from step 2 P_API_HOOK_CALL_TYPE => ‘PP’, P_SEQUENCE => L_SEQUENCE, P_ENABLED_FLAG => ‘Y’, P_CALL_PACKAGE => ‘XX_PKG’, — CUSTOM PACKAGE P_CALL_PROCEDURE => ‘XX_Pro’, — CUSTOM PROCEDURE P_API_HOOK_CALL_ID => L_API_HOOK_CALL_ID, P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER); END; Step 5 Run Pre-Processor program to implement user hook and to work as per requirement DECLARE l_module_id  NUMBER; –Pass the module id from step 1 BEGIN hr_api_user_hooks_utility.create_hooks_one_module (l_module_id);  COMMIT; END; If changes made in custom package procedure no need to run pre-processor program again, but if package procedure name changed then running pre-processor program applied. Step 6 After custom code hooked with standard code then you can find the call of your custom package procedure in table HR_API_HOOK_CALLS SELECT * FROM HR_API_HOOK_CALLS WHERE api_hook_id = API Hook id from step 2; If Pre-Processor is successful STATUS will be ‘V’. If Pre-Processor is not successful STATUS will be ‘I’. Pre-Processor is not yet processed STATUS will be ‘N’. There are 3 special procedures that maintain data in this table:

• create_api_hook_call.
• update_api_hook_call.
• delete_api_hook_call.

---

Finally Some Procedures to delete or update User Hook

• If you want to disable custom package procedure, use API UPDATE_API_HOOK_CALL to set p_enabled_flag to ‘N’ then run pre-processor program again to recreate hook package body, To re-enable custom package body use same API and set p_enabled_flag to ‘Y’ and run pre-processor program

hr_api_hook_call_api.update_api_hook_call    (p_validate     => false     p_effective_date  => TO_DATE(sysdate, ‘DD-MON-YYYY’)     p_api_hook_call_id    =>  *api_hook_call_id from table of hook calls     p_sequence   => *sequence from table of hook calls     p_enabled_flag  => ‘Y’  *enabled_flag from table of hook calls     p_call_package => ‘XX_PKG’, *call package from table of hook calls     p_call_procedure => ‘XX_Pro’ *call procedure from table of hook calls     p_object_version_number => out_object_version_number);     *originally generated from step 4  

• Similarly to delete the call to remove the extended logic business use the API DELETE_API_HOOK_CALL and run pre-processor afterwards

hr_api_hook_call_api.delete_api_hook_call (p_validate     =>   false  p_api_hook_call_id     =>   *api_hook_call_id from table of hook calls  p_object_version_number => *object_version_number from table of hook calls);    *originally generated from step 4

---

Hope you enjoyed this topic and find it useful, Thank you for your time.

Sherif ElZeniny