User Hook in iRecruitment

Many Self Service Web applications now use API calls to validate data prior to completing transactions. Back-in-the-day, some of the Oracle Forms used direct DML statements which would make it impossible to apply additional data validation without doing a customization to the form (or in the case of Self Service, the page). If the same API is used in the PUI and in Self Service, one User Hook can be used to enforce additional business logic in two data entry points.

To demonstrate how a User Hook can implement additional business logic to a transaction, we will use the iRecruitment module and the Candidate Applicant Details page. At the bottom of the page there is a section to assign a rating:





By default, the transaction will only allow values between 0 and 100:




Suppose you wanted to restrict the entry to values <= 5 but do not want to do a complicated JDeveloper extension to customize the logic in the page. A PL/SQL User Hook can be developed to prevent the transaction from going any further based on rules you code. First, we must find which API is being used by the page so that we can attach a User Hook to it. Finding the User Hook involves the following steps:

1. Enabling Diagnostics
2. Creating test transaction with value > 100
3. Reviewing the log

To enable Diagnostics, set the Profile Option "FND: Diagnostics" to Yes for your user. Follow these steps to turn Diagnostics on for the application:

1. Click the Diagnostics link in the upper right corner
2. Choose "Show Log on Screen" and click "Go"
3. Set the level of debugging by choosing "Statement", which is the lowest level and click "Go"

Provided Oracle exposes the API call in the EO Implementation, you can acquire this information from the "About this Page" as shown in the following screen print:


Diagnostics is now enabled. You will notice that the bottom of each Self Service page now has extensive debugging information. The following three screen prints show the results of steps 1-3:






Next, initiate a test transaction with a Rating > 100 to trigger the default API validation. Search for the word ERROR in the log at the bottom of the page:





The debug statement indicates that the $JAVA_TOP/oracle/apps/per/schema/server/PerAllAssignmentsFEOImpl.class file contains the API call. Retrieve this file from the middle tier of your Oracle Applications environment and decompile it:




The API being used by the page is HR_ASSIGNMENT_SWI.update_apl_asg. Examining this API from TOAD shows us that it calls HR_ASSIGNMENT_API.update_apl_asg:




Querying the tables HR_API_MODULES and HR_API_HOOKS will indicate which User Hook will be used in conjunction with the assignment API. "BP" in the HR_API_MODULES.api_module_type column indicates it is a Business Process Hook:




The HR_API_HOOKS.api_hook_type column indicates there is a Before Process (BP) and After Process (AP) hook available. Using the Before Process will allow the transaction to stop before any DML is performed by the application:




At this point, some basic PL/SQL must be developed in order to enforce any additional business logic that needs to be added to Oracle's standard API. The steps to do this are as follows:

1. Develop a PL/SQL package
2. Register a User Hook
3. Run User Hook Preprocessor
4. Bounce Apache

The HR_ASSIGNMENT_API.update_apl_asg API has many arguments. Since our example involves restricting the value of the Rating ID, we only need to pass this parameter into our PL/SQL code. Our PL/SQL code will refer specifically to an Application Message called "HACK_APPLICANT_RATING_MESG", which will be created later on.

PL/SQL package spec:

create or replace package hack_hook_pkg as
--
procedure check_rating_value (p_applicant_rank in number);
--
end hack_hook_pkg;
/
show errors;

PL/SQL package body:

create or replace package body hack_hook_pkg as
--
procedure check_rating_value (p_applicant_rank in number) is
--
custom_error                 exception;
--
begin
   --
   if p_applicant_rank > 5 then
      raise custom_error;
   end if;
   --
exception
   when custom_error then
      hr_utility.set_message(800, 'HACK_APPLICANT_RATING_MESG');
      hr_utility.raise_error;
end check_rating_value;
--
end hack_hook_pkg;
/
show errors;

Register the User Hook with this next anonymous PL/SQL block. The hr_api_hook_call_api defines your User Hook with the name of the PL/SQL package.procedure name:

set serveroutput on size 1000000
declare
--
ln_api_hook_call_id            number;
ln_object_version_number       number;
ln_api_hook_id                 number;
--
begin
   --
   select ahk.api_hook_id
     into ln_api_hook_id
     from hr_api_hooks ahk, hr_api_modules ahm
    where ahm.module_name = 'UPDATE_APL_ASG'
      and ahm.api_module_type = 'BP'
      and ahk.hook_package = 'HR_ASSIGNMENT_BK5'
      and ahk.hook_procedure = 'UPDATE_APL_ASG_B'
      and ahk.api_hook_type = 'BP'               -- Before Process
      and ahk.api_module_id = ahm.api_module_id;
   --
   -- insert a row into HR_API_HOOK_CALLS
   --
   hr_api_hook_call_api.create_api_hook_call
      (p_effective_date        => to_date('30-NOV-2001','DD-MON-YYYY')
      ,p_api_hook_id           => ln_api_hook_id
      ,p_api_hook_call_type    => 'PP'
      ,p_sequence              => 1
      ,p_enabled_flag          => 'Y'
      ,p_call_package          => 'HACK_HOOK_PKG'
      ,p_call_procedure        => 'CHECK_RATING_VALUE'
      ,p_api_hook_call_id      => ln_api_hook_call_id
      ,p_object_version_number => ln_object_version_number);
   --
   dbms_output.put_line('Registered HOOK...');
   --
exception
   when others then
      dbms_output.put_line(substr('Error: '||sqlerrm,1,255));
end;
/

The last coding step is to run the User Hook Pre-Processor using this anonymous PL/SQL block:

set serveroutput on size 1000000
declare
--
cursor c1_cur is
   select api_module_id
     from hr_api_modules
    where module_name = 'UPDATE_APL_ASG';
--
begin
   --
   for c1 in c1_cur loop
      hr_api_user_hooks_utility.create_hooks_one_module( p_api_module_id =>   c1.api_module_id);
   end loop;
   --
exception
   when others then
      dbms_output.put_line(substr(sqlerrm,1,255));
end;
/

Note: All of the scripts in the previous section were developed to be compiled at the SQL*Plus prompt as shown here:




A new record will be added in the table HR_API_HOOK_CALLS:




At this point, all the technical work is complete for this extension. We must create our custom error message by doing the following:

(N) Functional Administrator -> Core Services -> Messages




Bounce Apache and retest the transaction: