How To Use API User Hooks

Hi All, In this document i will explain the advantages and disadvantages of User Hooks, and how to implement them. 1. General Introduction 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 Human Resources Management System(HRMS.)

---

User Hooks offer similar functionality to the Custom Library, but at the database end rather than the front end. The table HR_API_HOOKS contains all the hooks available for each API module listed in the HR_API_MODULES table. 2. 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. 3. Disadvantages A customized procedure called by a User Hook will only work if the API that the call has been added to is executed. It sounds obvious. However, at the time of writing there are still a large number of forms that don’t call APIs. The Enter Person Information form is one of them. Therefore, any hook added to the Create_Employee API will not be executed from this form. It is policy to eventually convert all existing forms to call APIs but this will not be a short term process. Unfortunately, there is no list available which indicates which forms call APIs and which do not. The only certainty is that all new forms will call APIs. A sensible approach to any implementation of configuration logic, therefore, if required to be executed by a standard form, is for the user to use a hook if the form calls a supported API, and custom library if not. 4. Implementing User Hooks There are basically 4 steps to implementing API 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. Step 1 will be dealt with in more detail in Section 5 , and 2 to 4. in Section 6. Please Be With Me 5. Available User Hooks Hooks are not available to alternative interface APIs,such as CREATE_GB_EMPLOYEE. Any extra logic should be associated with the main API. In this case, CREATE_EMPLOYEE. A list of alternative APIs,current at the time of writing follows:

Select module_name

From hr_api_modules

Where api_module_type = 'AI'

User Hooks are also not provided for utility type APIs such as CREATE_MESSAGE_LINE. A list of each API,it’s hooks, and the parameters available to a procedure called by that hook, can be obtained by running the script $PER_TOP/admin/sql/hrahkpar.sql. There are 5 different types of User Hook. Two for Business Process APIs (Before Process and After Process), and 3 more for Row Handler APIs (After Insert,After Update and After Delete). 5.1 Business Process Hooks The Business Process hooks should be used if extra logic is required on top of the standard business process logic. For instance, CREATE_EMPLOYEE, UPDATE_ELEMENT_ENTRY,etc. A full list can be obtained by running the following script: select module_name from hr_api_modules where api_module_type=’BP’ The two types of Business Process hook available are: 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 following sql retrieves the hook details of the After Process hook for the CREATE_EMPLOYEE business process.

select ahk.api_hook_id, ahk.hook_package, ahk.hook_procedure

from hr_api_hooks ahk, hr_api_modules ahm

where ahm.module_name='CREATE_EMPLOYEE'

and ahm.api_module_type = 'BP'

and ahk.api_hook_type = 'AP'

and ahk.api_module_id=ahm.api_module_id ;

6.1 Using User Hooks After choosing the type of hook required and the location for it, the hook code has to be written. It then needs to be registered, and finally the hook package has to be modified to call it. The example used in this section describes the implementation of an After Process hook in the CREATE_EMPLOYEE Business Process API. The hook code must be written in a PL/SQL server-side package procedure. The procedure is always called if registered (unless the main validation logic errors first), and, any conditional logic must be implemented in the code and an application error raised if required. No commits or rollbacks are allowed in the hook procedure. These are always performed after the API has been called whether it be in a PL/SQL wrapper or form. When the PL/SQL package has been created, it must be compiled successfully on the database. Fig 1 below illustrates how a user hook can be used to implement some extra validation on name and nationality. An error is raised if the validation fails.

create or replace Package scoop_nationality_check as

procedure polish_name_check

(p_last_name         in  varchar2

,p_nationality       in  varchar2);

end scoop_nationality_check;

Create or Replace Package Body scoop_nationality_check as

procedure polish_name_check

(p_last_name         in  varchar2

,p_nationality       in  varchar2)

is

begin

-- When the first name entered is a polish name then check that the

-- nationality entered is Polish

if p_last_name = 'Chrosicka' then

if p_nationality != 'POL' then

dbms_standard.raise_application_error

(num => -20999

,msg => 'Nationality must be Polish');

end if;

end if;

end polish_name_check;

end scoop_nationality_check;

6.2 Registering the User Hook The next step is to link the custom package procedure referred to in section 6.1 to the hook package. The table that holds this information is HR_API_HOOK_CALLS. SQL> DESC HR_API_HOOK_CALLS; There are 3 special procedures that maintain data in this table. These are * hr_api_hook_call_api.create_api_hook_call * hr_api_hook_call_api.update_api_hook_call * hr_api_hook_call_api.delete_api_hook_call below shows how to register 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('01-JUL-1999','DD-MON-YYYY'),

p_api_hook_id           => 63,

p_api_hook_call_type    => 'PP',

p_sequence              => 3000,

p_enabled_flag          => 'Y',

p_call_package          => 'SCOOP_NATIONALITY_CHECK',

p_call_procedure        => 'POLISH_NAME_CHECK',

p_api_hook_call_id      => l_api_hook_call_id,

p_object_version_number => l_object_version_number);

end;

6.3 Running the Pre-Processor Adding rows to the HR_API_HOOK_CALLS table by itself is not sufficient to enable this extra logic to be called. The pre-processor program must be called first. This will look at the definitions in the table and build another package body in the database which is known as the hook package body. If successful, the pre-processor will hard code the calls to the custom package procedures into the hook package body.  If no extra logic is implemented the hook package body is still created but without any calls to custom procedures. During upgrades the pre-processor program will be automatically called to create the hook package bodies. To run the pre-processor run one of the following commands: cd $PER_TOP/admin/sql Log into SQLPLUS as the APPS user SQL> @hrahkall.sql; Good Luck, if you need any support you can contact me on 00201090016023 or 00201006262837