Implementing User Hooks in Oracle EBS R12

For basic understanding of user hook please review below link,

Understanding User Hook in Oracle EBS R12

There are several steps to implement user hook,Please note user hook is not available for all API’s.

1.Identifying User Hook

So first step is to check whether user hook is available on a required API. Using below query you will able to find all available user hook.

API_MODULE_TYPE  can be ‘BP’ for business process or ‘RH’ Row Handlers

 SELECT AHK.API_HOOK_ID,
 AHK.API_MODULE_ID,
 AHK.HOOK_PACKAGE,
 AHK.HOOK_PROCEDURE,
 AHM.MODULE_NAME,
 AHM.API_MODULE_TYPE,
 AHK.API_HOOK_TYPE 
 FROM HR_API_HOOKS AHK, HR_API_MODULES AHM
 WHERE 1=1
 AND AHM.API_MODULE_TYPE = 'BP'--
 AND AHK.API_MODULE_ID = AHM.API_MODULE_ID
 order by API_MODULE_ID; --2759 for EIT on person level

Note API_MODULE_ID for required register User Hook in step 2 & running Pre-Processor in step 3

2. Register User Hook

Register User Hook with custom package, procedure and API_MODULE_ID identified in step 1,

DECLARE
L_API_HOOK_ID NUMBER:= 2759;
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 =>L_API_HOOK_ID,
P_API_HOOK_CALL_TYPE => 'PP',
P_SEQUENCE => L_SEQUENCE,
P_ENABLED_FLAG => 'Y',
P_CALL_PACKAGE => 'XXCUST_HR_ADV_SALARY_REQUEST', --- Custom Package to be called
P_CALL_PROCEDURE => 'XX_ADVANCE_SALARY_PROCESSING ', -- Custom Procedure to be called
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 ;

3. Run Pre-Processor to activate created User Hook

It is required to run pre-processor in order to activate newly created User Hook, Use the API_MODULE_ID identified from step 1

Using PL/SQL Script

DECLARE
L_API_MODULE_ID NUMBER := 1226; 
BEGIN
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);
END;

Using Putty

Run following is the command in Putty

(We need to find location of file : hrahkone.sql)

> cd $PER_TOP/admin/sql or > cd $PER_TOP/ patch/115/sql or any other suggested by DBA

(Open sqlplus)

> sqlplus username/password

(Run the file hrahkone.sql)

> @hrahkone.sql

It will ask for api_module_id which we found at point 2

> Enter value for api_module_id: 1226

Check User Hook Status

User below query to check status of User Hook

SELECT * FROM HR_API_HOOK_CALLS WHERE api_hook_id =2759

 

 

 

 

 

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

Important Points

User Hook Statuses

There could be three statuses for user hook

• V (Valid)
• I (In Valid)
• N (Not Active)

If status is not changing to ‘V’ and showing as ‘N’ it could be due to pending commit statement, Use below link to resolve issue

After Running the Pre Processor Program for an API User Hook, the Status in HR_API_HOOK_CALLS Table Did Not Change to ‘V’. (Doc ID 1457575.1)

Deleting User Hook

In case you don’t need any user hook then you can delete it, Use below query to identify API_HOOK_CALL_ID & OBJECT_VERSION_NUMBER

SELECT * FROM HR_API_HOOK_CALLS WHERE api_hook_id =2759

Now use below script to delete user hook,

Declare
l_api_hook_call_id number := 1340;
l_object_version_number number := 20;
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;

List of User Hook (Business Processes & Row Handlers)

Business Processes

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