Introduction to User Hooks

Introduction to User Hooks

User hooks allows to add custom functionality to oracle supplied public API. Oracle EBS supply lot if public API to Insert/Update/Delete data in Oracle EBS tables. While doing so, the user might want to perform some custom validations/processing. This can be achieved with the help of User Hooks.

Why do I need a user hook if I can achieve the same using database triggers?
Oracle recommends not to use any database triggers on any of the EBS Tables as it tampers the integrity of the data because most of the tables in EBS are interrelated and an API is able to perform the operations on all related tables in a designated way.

How to identify if the API has user hook?
Run below SQL to find out if user hook is available for the API.
SELECT * FROM HR_API_MODULES
WHERE MODULE_PACKAGE LIKE '<Public API Package Name>'

Ex: Below query returns the hooks available for Special Information Types(SIT)
SELECT * FROM HR_API_MODULES
WHERE MODULE_PACKAGE LIKE 'HR_SIT_API'

If the above query returns more than one row, then it has user hooks enabled.

Can I perform custom processing on INSERT/UPDATE/DELETE operations?
Yes, it's possible but the API should have provision for the same. In the above example, it returns three rows with MODULE_NAME as CREATE_SIT, UPDATE_SIT and DELETE_SIT. So in this case it's possible to introduce custom processing for all above operations.

Like database triggers, can we execute our custom code before/after the operation?
Yes, it's possible and it's called Hooks. There are two types of hooks as shown below

• Before Process(BP): Before the processing starts, it executes custom code first.
• After Process(AP) : After the processing is completed, it executes custom code.

How to identify the hooks available for an API?

User Hooks are stored in a table called HR_API_HOOKS. This table has a column called Hook Type with values of BP(Before Process), AP(After Process) to identify type of hook.

Ex: Below query returns the hooks available for HR_SIT_API

SELECT HOOKS.* FROM HR_API_HOOKS HOOKS, HR_API_MODULES MODULES 

WHERE MODULES.MODULE_PACKAGE LIKE 'HR_SIT_API'

AND   HOOKS.API_MODULE_ID = MODULES.API_MODULE_ID;

Are user hooks upgrade safe?

Yes, custom code introduced using user hooks is upgrade safe. Might need to regenerate the user hooks if it's really required.

The next blog Creating User Hooks, discuss about how to create User Hook.