Steps For Creating User Hook

Steps For Creating User Hook :Oracle HRMS

Step 1: Find the API for which HOOK has to write.

            There are mainly two tables:

             1)HR_API_HOOKS

             2)HR_API_MODULES

Note:For understanding types of hook search in oracle metalink.

Step 2: Create A PL/SQL  Procedure which fits requirements.

Step 3: Now Register created procedure into Required Hook:

             API for Registering User Hook:

                   . 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

Step 4: Once Hook registered. We need to run the Pre Processors which will taken care by DBA.

Step 5: Once done with all above steps verify the status of user hook in the table called:

             HR_API_HOOK_CALLS

"Team Work" More We Less Me.

       Sameer Patil

How to Register/Delete Procedure in User Hook

1)     Create API Hook Call (Register Procedure in User Hook)

·   Package Name: hr_api_hook_call_api.create_api_hook_call

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-JAN-1950,'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;

         i.            The hook mechanism only supports calls to package procedures currently so api_hook_call_type must be PP.

       ii.            Sequence numbers > 2000 are recommended, as sequences < than 2000 are reserved for Oracle seeded logic which needs to be processed first. Please note that you can add any number of hooks to the same API, and order them using the sequence.

2)     Delete  API Hook Call (Delete Registered Procedure in User Hook)

·   Package Name: hr_api_hook_call_api.delete_api_hook_call

declare

l_api_hook_call_id      number := 2;

l_object_version_number number := 3;

begin

hr_api_hook_call_api.delete_api_hook_call

 (p_validate              => false,

  p_api_hook_call_id      => l_api_hook_call_id,

  p_object_version_number => l_object_version_number);

end;

Demo on how to register Procedure in User Hook:

 

--Step 1 

select * from hr_api_modules where module_name  ='CREATE_PERSON_ABSENCE'

-- Step 2

select ahk.api_hook_id,ahk.api_hook_type,

        ahk.hook_package,

        ahk.hook_procedure from hr_api_hooks ahk where

 api_module_id = 1739

-- Step 3 -- Get All registered Packages and Prcoedures

select * from hr_api_hook_calls  where api_hook_id = 3868 -- After Proccess

-- Step 3 – Complete Query

select ahk.api_hook_id,ahk.api_hook_type,

        ahk.hook_package,

        ahk.hook_procedure

   from hr_api_hooks ahk,

        hr_api_modules ahm

 where ahm.module_name='CREATE_PERSON_ABSENCE' 

  and ahk.api_module_id=ahm.api_module_id

-- How to Register Procedure to 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-JAN-1950','DD-MON-YYYY'),

  p_api_hook_id           => 3868,-- API HOOK ID

  p_api_hook_call_type    => 'PP',

  p_sequence              => 3100,-- SEQ NO

  p_enabled_flag          => 'Y',

  p_call_package          => 'KFSH_WF_NOTIFY_PKG', -- Package Name

  p_call_procedure        => 'KFSH_NOTIFY_UNPAID_LEAVE_P', -- Procedure Name

  p_api_hook_call_id      => l_api_hook_call_id,

  p_object_version_number => l_object_version_number);

 

  dbms_output.put_line('Procedure Successfully Registered');

 

  exception

  when others then

    dbms_output.put_line(sqlerrm);

 

end;

-- Run the Pre Processor ----

DECLARE

BEGIN

    hr_api_user_hooks_utility.create_hooks_one_module(1739); -- Module ID

   

    dbms_output.put_line('Pre-Preocessor Run Successfully');

 

 

   exception

      when others then

        dbms_output.put_line(sqlerrm);

END;

User Hooks in Oracle HRMS

User Hooks in Oracle HRMS

API User Hooks refers to points in the API execution to which customers can

attach or extend business logic. User-defined code can be “hooked” into these

predefined locations, and executed in conjunction with the API call.

User code can: extend validation on Oracle HRMS tables, maintain data in

external customer-specific tables (not part of Oracle Applications)

simultaneously with the Oracle HRMS updates through the API, or send alerts at

particular Oracle HRMS events.

The explicit limitation for customer extensions remains—any and all HRMS

data manipulation must be performed by HRMS APIs.

User Hooks offer similar functionality to the Custom Library, but at the database

rather than in the Forms front end.

User Hooks allow logic extensions without changing delivered API code. Hooks

are regenerated at upgrade. However, since schema changes may occur at any

time, all custom procedures should be tested after upgrade. Additionally, API

Hooks are available to any interface that calls the API, such as: Forms, Self-

Service, and Data Pump.

Not all HRMS Forms use APIs; a trace can identify whether a Form uses APIs.

Only Forms that call APIs will execute a User Hooked code. If the Form does

not use APIs, the same code extension can be implemented in Custom Library.

The HR_API_HOOKS table holds all available API hooks for APIs in

HR_API_MODULES table.

Implementing User Hooks

Four steps are required to implement API User Hooks:

1. Determine the API to which you need to hook extra logic

2. Write the PL/SQL procedure that executes your logic.

3. Register your procedure with one or more specific user hooks.

4. Run the pre-processor program that hooks your PL/SQL procedure to the

hook(s). 

Oracle HRMS has several types of hooks. Only Business Process Hooks

correspond to Business Process APIs. They are used when logic must extend

beyond the standard business process. Only Business Process Hooks are

Supported for Customer use. A list can be obtained by running the following

query:

select module_name

from hr_api_modules

where api_module_type='BP';

The $PER_TOP/admin/sql/hrahkpar.sql script provides listing of each API, its

hooks, and corresponding procedure parameters.Sample Hook Implementation

Get Hook Information

SELECT      ahk.api_hook_id,

ahk.api_module_id,

ahk.hook_package,

                    ahk.hook_procedure

  FROM       hr_api_hooks ahk, hr_api_modules ahm

 WHERE      ahm.module_name = 'CREATE_PERSON_ABSENCE'   -- Module Name

                    AND ahm.api_module_type = 'BP'

AND ahk.api_hook_type = 'AP'

                    AND ahk.api_module_id = ahm.api_module_id

Hook Registration

DECLARE

l_api_hook_call_id      NUMBER;

l_object_version_number NUMBER;

l_sequence              NUMBER;

BEGIN

SELECT HR_API_HOOKS_S.NEXTVAL

  INTO   l_sequence

 FROM   dual;

 Hr_Api_Hook_Call_Api.create_api_hook_call

 (p_validate                 => FALSE,

  p_effective_date           => TO_DATE('01-JAN-1952','DD-MON-YYYY'),

  p_api_hook_id              =>

  p_api_hook_call_type       => 'PP',

  p_sequence                 => l_sequence,

  p_enabled_flag             => 'Y',

  p_call_package             => 'LMHR_HOOK_VALIDATIONS_PKG',    -- Custom Package

 p_call_procedure           => UPPER('Validate_Objectives_Weight'),  -- Custom procedure

 p_api_hook_call_id         => l_api_hook_call_id,

 p_object_version_number    => l_object_version_number);

  DBMS_OUTPUT.PUT_LINE('l_api_hook_call_id '|| l_api_hook_call_id);

END ;

Run the pre-processor script

        Run pre-processor script hrahkone.sql with module name as parameter (PER_TOP/admin/sql/hrahkone.sql).It compile API hook. After running this your APIhook Package should have custom procedure call.

Delete custom code from APIuser Hook

      Get Hook Call ID and Object Version_number

Select  api_hook_call_id,object_version_number from HR_API_HOOK_CALLS where call_package like ‘LMHR%’ and call_procedure = UPPER('Validate_Objectives_Weight')

Hook Deletion

BEGIN

 Hr_Api_Hook_Call_Api.delete_api_hook_call  (     p_validate         => FALSE,

                                                                                p_api_hook_call_id => ,

                                                                               p_object_version_number =>2

                                                                           );

  DBMS_OUTPUT.PUT_LINE('deleted Successfully');

END;