Thursday 12 November 2015

Oracle API User Hooks


Hi All,

Happy Diwali :)

I would like to give you some idea on Oracle User hooks.

You might have come across situations wherein we must modify the Oracle standard API to get some info into custom tables/extra validations/to send alerts/etc. However, Oracle doesn’t recommend modifying the standard API.

What is API?

API, an abbreviation of Application Program Interface, is a set of routines, protocols, and tools for building software applications. The API specifies how software components should interact and APIs are used when programming graphical user interface (GUI) components.

What is User Hook?

User Hook, is a pl/sql code used to execute from the standard API. 
Oracle has supplemented the core product API functionality by the addition of API user hooks.

When the API processing reaches a user hook, core product processing stops and any customer specific logic for that event is executed. Then, if no errors occur, the main API processing resumes.

You can use such extra logic to add functionality not supplied directly by Oracle Applications.

NOTE: You should not manually edit the API source code files supplied by Oracle If you do modify these codes, Oracle Applications will be unable to support the product, and upgrades may not be possible. Oracle Applications only supports direct calls to the published APIs. Direct calls to any other server-side package procedures or functions that are written as part of the Oracle HRMS Applications suite are not supported.

Hence, the conclusion is User hook is used to add extra logic to the Business APIs using User Hooks

Below diagram shows the overview of how the custom logic executes in the Oracle standard API:


User Hook Tables:

HR_API_HOOKS - Contains all the API hooks
HR_API_MODULES – Module list in which hooks available

Steps to 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.
Note: Oracle has not given the user hook option to all the forms OR APIs. 
HR_API_HOOKS table gives the hook details
Use HR_API_MODULES table to get the API names (Module Package) and the procedure name (Module Name) with respective Module Type.

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.

TYPES of User Hook:

There are 5 different types of User Hooks (Module Type in the HR_API_MODULES):
> Business Process APIs:
        1. AD – After Delete
        2. AI – After Insert
> Row Handler APIs:
        1. AP – After Process
        2. AU – After Update
        3. BP – Before Process

Example:



Business Process User 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.

SELECTahk.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_EMPLOYEE'
           and ahm.api_module_type = 'BP'
           and ahk.api_hook_type = 'AP'
           and ahk.api_module_id=ahm.api_module_id;




Row Handler User Hooks:

The Row Handler hooks should be used if extra logic is required prior to
performing an Insert,Update or Delete on a specific table. As all the main APIs
call the row handlers, these hooks would be executed by any API that updates
the specific table. 

A full list of the Row Handler APIs can be obtained by running the following sql:

SELECT module_name 
  FROM  hr_api_modules
WHERE api_module_type='RH';

The following sql retrieves hook details for the After Insert hook for the PER_ALL_PEOPLE_F table:

SELECT ahk.api_hook_id,
            ahk.hook_package,
            ahk.hook_procedure
  FROM  hr_api_hooks ahk,
            hr_api_modules ahm
WHERE (ahm.module_name='PER_ALL_PEOPLE_F'
            OR ahm.module_name='PER_PEOPLE_F')
     AND ahm.api_module_type = 'RH'
     AND ahk.api_hook_type = 'AI'
     AND ahk.api_module_id=ahm.api_module_id;


Core Product Logic

Core Product Logic is split into a number of components. For tables that can be altered
by an API there is an internal row handler code module. These rows handlers are
implemented for nearly all the tables in the system where APIs are available. They
control all the insert, update, delete and lock processing required by the main APIs. For
example, if a main API needs to insert a new row into the PER_ALL_PEOPLE_F table it
will not perform the DML itself. Instead it will execute the PER_ALL_PEOPLE_F row handler

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.

Writing the custom PL/SQL procedure
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. 

NOTE: 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.

Example:
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;
/

Registering the User Hook
The next step is to link the custom package procedure created above

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);
EXCEPTION WHEN OTHERS
 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,300)); -- Handle exception

END;

Points to be noted:

   a. See section ‘Business Process User Hooks’ to get api_hook_id. (Refer above queries)

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

   c. 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.

To Delete the user hook:

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);

EXCEPTION WHEN OTHERS
 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,300)); -- Handle exception
END;

The above call is deleting a specific user hook. Please note any changes made to the HR_API_HOOK_CALLS table, be they via the supplied procedures or not,
will only be effective once the pre-processor has been run.


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

OR

SQL> @hrahkone.sql

NOTE: The first script will create all hook package bodies, whilst the second will create hook package bodies for one API module only, and prompt for that api_module_id. Refer to the results of the query executed in above(first query) to obtain the api_module_id.

Tips on resolving Invalid Hook Packages

Occasionally when applying patches Hook Packages may fail to compile. This could
be for a variety of reasons. Running the report $PER_TOP/patch/115/sql/hrahkall.sql
will provide a comprehensive list of the invalid hook packages. 

Sometimes the problem may simply be resolved by reapplying the hook package

Refer HRMS Implementation guide for examples

To find the code used by a User Hook

You can use the hook name and type from the error message:

SELECT api_hook_id, hook_procedure 
   FROM hr_api_hooks
 WHERE api_hook_type = 'AP'
     AND hook_procedure LIKE  'UPDATE_ORG_INFORMATION%';

SELECT call_package, call_procedure, legislation_code
  FROM  hr_api_hook_calls
 WHERE api_hook_id = 5012; -- Got this value from the above query


The following query will provide you the additional information:

SELECT *
FROM   hr_api_hook_calls
WHERE api_hook_id IN (SELECT api_hook_id
                                        FROM hr_api_hooks
                                      WHERE api_module_id = (SELECT api_module_id
                                                                               FROM hr_api_modules
                                                                             WHERE module_name LIKE  'UPDATE_ORG_INFORMATION%'));


*************************************************************************************************

Please refer the below unpublished metalink note OR HRMS implementation guide for more info on User hooks:
> Understanding and Using Application Program Interface (API) User Hooks in Oracle HRMS [ID 73170.1]

> Refer ‘API User Hooks’ section in Oracle HRMS Implementation Guide

List of APIs

The following APIs support all the API event points (Before process; After process) :

applicant assignment
offer_apl_asg
update_apl_asg
contact relationship
create_contact_relationship
delete_contact_relationship
employee
actual_termination_emp
final_process_emp
create_employee
employee assignment
activate_emp_asg
suspend_emp_asg
final_process_emp_asg
update_emp_asg
actual_termination_emp_asg
employee assignment criteria
update_emp_asg_criteria
grade rate value
create_grade_rate_value
update_grade_rate_value
delete_grade_rate_value
job requirement
create_job_requirement
mass moves
mass_moves
pay scale value
create_pay_scale_value
update_pay_scale_value
delete_pay_scale_value
person address
create_person_address
update_person_address
personal payment method
create_personal_payment_method
update_personal_payment_method
position
create_position
update_position
position requirement
create_position_requirement
secondary applicant assignment
create_secondary_apl_asg
secondary employee assignment
create_secondary_emp_asg

Row Handlers

The Row Handlers on the following tables support all the Row Handler event points (After insert; After update; After delete) :

PER_ADDRESSES
PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_F
PAY_PERSONAL_PAYMENT_METHODS_F
PER_POSITIONS
PER_APPLICATIONS
PER_CONTACT_RELATIONSHIPS



This document is for my future reference and posting for others to share the knowledge. Please do suggest or comment which helps all.

Thank you.

Regards,
Rajesh



9 comments:

  1. Hello Friends, I am trying to to register a user hook and try to capture deleted SIT details into a custom table..Unfortunately my User hook is not registering for some reason. Kindly someone guide me.
    DECLARE
    L_API_HOOK_ID NUMBER := 3858; L_API_HOOK_CALL_ID NUMBER; L_OBJECT_VERSION_NUMBER NUMBER; L_SEQUENCE NUMBER;
    BEGIN
    SELECT APPS.HR_API_HOOKS_S.NEXTVAL INTO L_SEQUENCE FROM DUAL;
    APPS.HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL ( P_VALIDATE => FALSE,
    P_EFFECTIVE_DATE => TO_DATE ('01-JAN-1901', 'DD-MON-YYYY'), P_API_HOOK_ID => L_API_HOOK_ID,
    P_API_HOOK_CALL_TYPE => 'PP', P_SEQUENCE => L_SEQUENCE, P_ENABLED_FLAG => 'Y', P_CALL_PACKAGE => 'perc_sit_del_uh_pkg',
    P_CALL_PROCEDURE => 'delete_sit_b', 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);
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error in seeding user hook procedures: '|| SQLERRM); COMMIT; END;
    --to register user hook
    declare l_api_module_id number := 1761; begin apps.hr_api_user_hooks_utility.create_hooks_one_module (l_api_module_id);
    dbms_output.put_line('Success'); exception when others then dbms_output.put_line('Exception : '||SQLERRM); commit; end;
    --to delete user hook
    SELECT api_hook_call_id, object_version_number INTO ln_api_hook_call_id,ln_object_version_number FROM hr_api_hook_calls WHERE call_package = 'perc_sit_del_uh_pkg' AND call_procedure = 'delete_sit_b';
    ---------------

    ReplyDelete
    Replies
    1. Hi Sunil,
      Please try by setting NLS parameters and initialize apps if you are trying this outside the application.

      Cheers,
      Rajesh

      Delete
  2. CREATE OR REPLACE PACKAGE PERC.perc_sit_del_uh_pkg AS procedure delete_sit_b( p_person_analysis_id in number ,p_pea_object_version_number in number);
    end perc_sit_del_uh_pkg;
    ----------------
    CREATE OR REPLACE PACKAGE BODY PERC.perc_sit_del_uh_pkg AS
    PROCEDURE delete_sit_b (p_person_analysis_id IN NUMBER, p_pea_object_version_number IN NUMBER) IS
    l_employee_number VARCHAR2 (99); l_person_analysis_id NUMBER; l_segment2 VARCHAR2 (99); l_segment6 VARCHAR2 (99);
    BEGIN
    SELECT papf.employee_number, ppa.person_analysis_id, pac.segment2, pac.segment6 INTO l_employee_number, l_person_analysis_id, l_segment2,
    l_segment6
    FROM per_person_analyses ppa, per_analysis_criteria pac, per_all_people_f papf, hr_lookups HL
    WHERE ppa.analysis_criteria_id = pac.analysis_criteria_id AND papf.person_id = ppa.person_id AND EFFECTIVE_END_DATE = hr_general.end_of_time
    AND pac.id_flex_num = 50248 --LANGUAGE
    -- and papf.employee_number = 'BO690'
    AND HL.LOOKUP_CODE = pac.segment2 AND HL.ENABLED_FLAG = 'Y' AND ppa.person_analysis_id = p_person_analysis_id AND HL.LOOKUP_TYPE = 'PERC_LANGUAGES';
    INSERT INTO PERC.PERC_ITM_SIT_UH_CAPTURE (person_analysis_id, object_version_number, segment2, segment14,
    creation_date,
    last_update_date)
    VALUES (l_person_analysis_id, l_employee_number, l_segment2, l_segment6, SYSDATE, SYSDATE - 1);
    COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO PERC.PERC_ITM_SIT_UH_CAPTURE (person_analysis_id,
    object_version_number, segment2, segment14, creation_date, last_update_date)
    VALUES (l_person_analysis_id, l_employee_number, l_segment2, l_segment6, SYSDATE, SYSDATE - 1);
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error in seeding deleted data: ' || SQLERRM);
    END delete_sit_b;
    END perc_sit_del_uh_pkg;

    ReplyDelete
  3. I want to restrict objectives to 100% in Oracle Performance Management by Userhook. Kindly tell me the possibility.

    ReplyDelete
  4. I found it very helpful, but when i try to send notification/message within the custom hook package/procedure by creating workflow it cannot send! please help...

    wf_engine.createprocess(.,.,.);
    wf_engine.startprocess(.,.);

    ReplyDelete
  5. would you please explain ap inv custom validation hook proc

    ReplyDelete
  6. would you please explain ap inv custom validation hook proc

    ReplyDelete
  7. I want to share a testimony on how Le_Meridian funding service helped me with loan of 2,000,000.00 USD to finance my marijuana farm project , I'm very grateful and i promised to share this legit funding company to anyone looking for way to expand his or her business project.the company is funding company. Anyone seeking for finance support should contact them on lfdsloans@outlook.com Or lfdsloans@lemeridianfds.com Mr Benjamin is also on whatsapp 1-989-394-3740 to make things easy for any applicant. 

    ReplyDelete