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

Last updated on OCTOBER 16, 2023

Applies to:

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 XX table when creating a subordinate position

   CREATE OR REPLACE PACKAGE XX_USERHOOK_POSHIER_RH_PKG
   AS
   PROCEDURE XX_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 XX_USERHOOK_POSHIER_RH_PKG;
   /

   CREATE OR REPLACE PACKAGE BODY XX_USERHOOK_POSHIER_RH_PKG
   AS
   PROCEDURE XX_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 XX values (1) ;
   end if;
   END XX_RAISE_POSHIER_RH_EVENT_P;
   END XX_USERHOOK_POSHIER_RH_PKG;
   /

    
3.  select * from all_objects where object_name like 'XX_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 => 'XX_USERHOOK_POSHIER_RH_PKG', --— CUSTOM PACKAGE
   P_CALL_PROCEDURE => 'XX_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 XX table.(which means this user hook does not work.)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.