Enable or Create User Hooks, JTF_USER_HOOKS

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, a user may want to implement User Hooks for one of the following reasons:

To extend the validation of data beyond what the standard system provides.
To maintain data held in extra customer specific tables (not part of Oracle Applications) as it is entered.
To send alerts when particular events happen within Oracle Application
User Hooks offer similar functionality to the Custom Library, but at the database end rather than the front end.

Advantages
User Hooks allow extra business logic to be inserted in exactly the right place in the application without needing to bespoke any of the standard APIs. Upgrades are no problem as the hooks will be regenerated during the upgrade process. However, Oracle reserves the right to change the HR schema at any time, which may necessitate modifications to customized PL/SQL procedures.

The main advantages of hooks over custom library are that they only need to be implemented in one place whereas custom libary modifications could conceivably have to be made on several clients. They are also immediately available to any interface that calls the API. For example, Forms, Self Service, Data Pump, etc.


Enabling USER HOOKS
The Package CS_SERVICEREQUEST_PUB makes a call to the CS_SERVICEREQUEST_CUHK User Hook. This call is made depending on values stored in the table JTF_USER_HOOKS. For this call to be made, a row with Package Name as CS_SERVICEREQUEST_PUB and an appropriate API Name and the Enabled Flag set to 'Y' should be present in the Table.

Implementation Steps:
1) First you have to identify the particular API, which you are looking to modify/customize. The list of APIs, which support this concept, can be found in following table –
a)CRM Related APIs – Jtf_User_Hooks
You can use the following Query –
Select Pkg_Name,Api_Name ,processing_Type ,Execute_Flag From Jtf_User_Hooks

If User Hook Package has no Package Body
This is the Customer User Hook API. The Customers can add customization procedures here for Pre and Post Processing. Oracle only supplies the spec file.It is left to the customer to create the body for their customizations.

Example :You will need to make your own insert(s) into the JTF_USER_HOOKS table.

Example: Lets say you want to call your custom procedure after the charge details have been created. So, you want CS_CHARGE_DETAILS_CUHK.Create_Charge_Details_Post to be executed. To do this, you need to insert the following values into the jtf_user_hooks table

INSERT INTO jtf_user_hooks(USER_HOOK_ID

,PKG_NAME
,API_NAME
,PRODUCT_CODE
,PROCESSING_TYPE
,EXECUTE_FLAG
,USER_HOOK_TYPE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5)
VALUES(1000000 -- A large value so that no conflict
,'CS_CHARGE_DETAILS_PVT' -- Package Name
,'Create_Charge_Details' -- Procedure Name
,'CS' -- For Service
,'A' -- After, i.e. Create_charge_details_post
-- Replace A with B in the above line if you want to use Pre-User hook.
,'Y' -- Execute Flag
,'C' -- Customer User Hook
,1234 -- fnd_global.user_id
,sysdate
,1234 -- fnd_global.user_id
,sysdate
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,NULL);