User Hooks In R12

User Hooks

Oracle has provided user hooks to implement custom logic or validation on standard processes.

For example:
Creating a element entry when creating an absence
validating the DFF segments in Absence before creating a absence.
Validation on creating EITs, Element entries, absence, termination of employee etc
Custom hook package (User Hook package) is a custom package where we write procedures for doing the customizations. The user hook procedure should have same parameters as standard API module procedure which invokes the user hook.

Now lets go through the steps to attach a user hook. I am assuming the user hook for create absence after process.

Step 1 :
Get the Module id from table HR_API_MODULES. In my case the module name is like 'CREATE%ABSENCE%'. Hence I query for the module using the below query.

SELECT * FROM hr_api_modules WHERE module_name LIKE 'CREATE%ABSENCE%';

I get the api_module_id as 1731.
---------------------------------------------------------------------------
Step 2:
Next I query for hook id in table hr_api_hooks for after process. Note that 'AP' means After Process hook and 'BP' is Before Process hook.

SELECT * FROM hr_api_hooks WHERE api_module_id = 1731;

 I get  the api_hook_id  as 3840

--------------------------------------------------------------------------
Step 3:
If you know the Module name , hook package name and hook procedure , you can use the script below .
Script for attaching the hook:

--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 = 'CREATE_PERSON_ABSENCE'
      and ahm.api_module_type = 'BP'
      and ahk.hook_package = 'HR_PERSON_ABSENCE_BK1'
      and ahk.hook_procedure = 'CREATE_PERSON_ABSENCE_A'
      and ahk.api_hook_type = 'AP'               -- After 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('14-MAR-2014','DD-MON-YYYY')
      ,p_api_hook_id           => ln_api_hook_id
      ,p_api_hook_call_type    => 'PP'
      ,p_sequence              => 3029
      ,p_enabled_flag          => 'Y'
      ,p_call_package          => 'XXMUD_DISCIPLINARY_ACTION_PKG'--Custom Hook PKG
      ,p_call_procedure        => 'CREATE_UNAUTHORIZED_ABS_ENTRY'--Procedure for creating entries
      ,p_api_hook_call_id      => ln_api_hook_call_id
      ,p_object_version_number => ln_object_version_number);
   --
   DBMS_OUTPUT.PUT_LINE('Registered HOOK...'|| ln_api_hook_call_id );
   --
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR('Error: '||SQLERRM,1,255));
END;

-------------------------------------------------------
Step 4:
Next step is to run the pre-processor to the hook. Without running the pre-processor the user hook will not work.

DECLARE
l_module_id  NUMBER; --Pass the module id
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module (1282);
END;

COMMIT;
--------------------------------------------------------
Step 5:
Next step is to verify if hook is registered.

SELECT * FROM hr_api_hook_calls
WHERE call_package = 'XXMUD_DISCIPLINARY_ACTION_PKG';

If STATUS column is 'V' (Stands for Valid) and ENABLED_FLAG = 'Y' then you have successfully registered the user hook.

----------------------------------------------------------------------------
Deleting User Hook:

DECLARE
ln_object_version_number       NUMBER;
ln_api_hook_call_id            NUMBER;
BEGIN

-- Get the api_hook_call_id and object_version_number
SELECT api_hook_call_id, object_version_number
  INTO ln_api_hook_call_id,ln_object_version_number
  FROM hr_api_hook_calls
 WHERE hook_call_package = 'XXMUD_DISCIPLINARY_ACTION_PKG'
   AND hook_procedure = 'CREATE_UNAUTHORIZED_ABS_ENTRY';

--API to delete the user hook
hr_api_hook_call_api.delete_api_hook_call
               (p_api_hook_call_id     =>    ln_api_hook_call_id, -- API_HOOK_CALL_ID
                p_object_version_number => ln_object_version_number );  -- Object_version_number
   DBMS_OUTPUT.PUT_LINE('DELETED HOOK...');
 
EXCEPTION
WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR('Error: '||SQLERRM,1,255));
END;