Rowhandler User Hooks For Position Hirarchy Elements Not Working (Doc ID 2274772.1)

Last updated on SEPTEMBER 12, 2017

Applies to:

Oracle Human Resources - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Problem Statement:

When trying to create a user hook (user hook type is rowhandler) for position hierarchy elements, it is not working.

Steps to Reproduce:
The issue can be reproduced at will with the following steps:

  1.  Identify Row handler user hooks for Position Hierarchy

    select hak.api_hook_id, hak.hook_package,
    hak.hook_procedure,hak.api_hook_type ,ham.api_module_id,
    ham.module_name
    from hr_api_hooks hak, hr_api_modules ham
    where ham.module_name like 'PER_POS_STRUCTURE_ELEMENTS%'
    --and ham.api_module_type = 'BP'
    and hak.api_module_id=ham.api_module_id



  2. Create custom Package

    For example: 1 has been inserted in AKA table when creating a subordinate position

    CREATE OR REPLACE PACKAGE AKA_USERHOOK_POSHIER_RH_PKG
    AS
    PROCEDURE AKA_RAISE_POSHIER_RH_EVENT_P
    (
    p_pos_structure_element_id in number
    ,p_business_group_id in number
    ,p_pos_structure_version_id in number
    ,p_subordinate_position_id in number
    ,p_parent_position_id in number
    );
    END AKA_USERHOOK_POSHIER_RH_PKG;
    /

    CREATE OR REPLACE PACKAGE BODY AKA_USERHOOK_POSHIER_RH_PKG
    AS
    PROCEDURE AKA_RAISE_POSHIER_RH_EVENT_P
    (
    p_pos_structure_element_id in number
    ,p_business_group_id in number
    ,p_pos_structure_version_id in number
    ,p_subordinate_position_id in number
    ,p_parent_position_id in number
    )
    IS
    BEGIN
    If 1 = 1 then
    insert into AKA values (1) ;
    end if;
    END AKA_RAISE_POSHIER_RH_EVENT_P;
    END AKA_USERHOOK_POSHIER_RH_PKG;
    /

     
  3.  select * from all_objects where object_name like 'AKA_USERHOOK_POSHIER_RH_PKG'
  4. Register User Hook and run pre-processor

    DECLARE
    L_API_HOOK_ID NUMBER:= 3772;
    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 =>sysdate,-- TO_DATE('01-JAN-1951','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 => 'AKA_USERHOOK_POSHIER_RH_PKG', --— CUSTOM PACKAGE
    P_CALL_PROCEDURE => 'AKA_RAISE_POSHIER_RH_EVENT_P', --— 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 ;
    /

  5. select * from hr_api_hook_calls where api_hook_call_id = 1858;
  6. Test the user hook by creating a subordinate position under parent position(in the form Position hierarchy).
  7. There is no data in AKA table.(which means this user hook does not work.)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms