User Hooks in Oracle HRMS

User Hooks

There were many times we need to put some extra logic before or after happening of some business event. In Such cases, we use user hook API. It is a functionality provided in Oracle HRMS through which you can have more control on application with respect to implementing business rules.

In Oracle HRMS, Oracle has provided location in HRMS APIs, where customer can put his business logic. When API processing reaches a user hook, core product processing stops and any customer specific logic for that event is executed. After processing of customer specific logic, main API resumes its processing.

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

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

Step 3: Register created procedure into Required Hook:

Step 4: Run the Pre Processors to make the hook effective.

Step 5: Verify the status of user hook in table HR_API_HOOK_CALLS.

Case Study:

Absences validation - Let’s assume we want to put logic to stop a user if he applied annual leave more than 30 days. It should validate before Absence Creation.

Run the below query to show all person absence relevant APIs and select the correct API that matches our requirement. CREATE_PERSON_ABSENCE API is the one we are going to use so we should note its API_HOOK_ID and API_MODULE_ID. The API_HOOK_ID will be used at the time of registration of user hook and API_MODULE_ID will be used in running the per processor.

Query:

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 like ‘%_PERSON_ABSENCE%’

AND AHM.API_MODULE_TYPE = ‘BP’

AND AHK.API_HOOK_TYPE = ‘AP’

AND AHK.API_MODULE_ID = AHM.API_MODULE_ID;

Custom Package:
Creating the custom code for implementing the custom business rules.

CREATE OR REPLACE PACKAGE APPS.XXCUS_USERHOOK_PKG AS

PROCEDURE XXCUS_CREATE_ABS

(P_EFFECTIVE_DATE IN DATE,

P_PERSON_ID IN NUMBER,

P_BUSINESS_GROUP_ID IN NUMBER,

P_ABSENCE_ATTENDANCE_TYPE_ID IN NUMBER,

P_ABS_ATTENDANCE_REASON_ID IN NUMBER,

P_COMMENTS IN LONG,

P_DATE_NOTIFICATION IN DATE,

P_DATE_PROJECTED_START IN DATE,

P_TIME_PROJECTED_START IN VARCHAR2,

P_DATE_PROJECTED_END IN DATE,

P_TIME_PROJECTED_END IN VARCHAR2,

P_DATE_START IN DATE,

P_TIME_START IN VARCHAR2,

P_DATE_END IN DATE,

P_TIME_END IN VARCHAR2,

P_ABSENCE_DAYS IN NUMBER,

P_ABSENCE_HOURS IN NUMBER,

P_AUTHORISING_PERSON_ID IN NUMBER,

P_REPLACEMENT_PERSON_ID IN NUMBER,

P_ATTRIBUTE_CATEGORY IN VARCHAR2,

P_ATTRIBUTE1 IN VARCHAR2,

...          – Please include all the attribute1 to attribute20

P_ATTRIBUTE20 IN VARCHAR2,

P_PERIOD_OF_INCAPACITY_ID IN NUMBER,

P_SSP1_ISSUED IN VARCHAR2,

P_MATERNITY_ID IN NUMBER,

P_SICKNESS_START_DATE IN DATE,

P_SICKNESS_END_DATE IN DATE,

P_PREGNANCY_RELATED_ILLNESS IN VARCHAR2,

P_REASON_FOR_NOTIFICATION_DELA IN VARCHAR2,

P_ACCEPT_LATE_NOTIFICATION_FLA IN VARCHAR2,

P_LINKED_ABSENCE_ID IN NUMBER,

P_BATCH_ID IN NUMBER,

P_CREATE_ELEMENT_ENTRY IN BOOLEAN,

P_ABS_INFORMATION_CATEGORY IN VARCHAR2,

P_ABS_INFORMATION1 IN VARCHAR2,

…       – Please include all the abs_information1 to abs_information30

P_ABS_INFORMATION30 IN VARCHAR2,

P_ABSENCE_CASE_ID IN NUMBER);

END XXCUS_USERHOOK_PKG;

/

CREATE OR REPLACE PACKAGE BODY APPS. XXCUS_USERHOOK_PKG

PROCEDURE XXCUS_CREATE_ABS (

P_EFFECTIVE_DATE IN DATE,

P_PERSON_ID IN NUMBER,

P_BUSINESS_GROUP_ID IN NUMBER,

P_ABSENCE_ATTENDANCE_TYPE_ID IN NUMBER,

P_ABS_ATTENDANCE_REASON_ID IN NUMBER,

P_COMMENTS IN LONG,

P_DATE_NOTIFICATION IN DATE,

P_DATE_PROJECTED_START IN DATE,

P_TIME_PROJECTED_START IN VARCHAR2,

P_DATE_PROJECTED_END IN DATE,

P_TIME_PROJECTED_END IN VARCHAR2,

P_DATE_START IN DATE,

P_TIME_START IN VARCHAR2,

P_DATE_END IN DATE,

P_TIME_END IN VARCHAR2,

P_ABSENCE_DAYS IN NUMBER,

P_ABSENCE_HOURS IN NUMBER,

P_AUTHORISING_PERSON_ID IN NUMBER,

P_REPLACEMENT_PERSON_ID IN NUMBER,

P_ATTRIBUTE_CATEGORY IN VARCHAR2,

P_ATTRIBUTE1 IN VARCHAR2,

…          – Please include all the attribute1 to attribute20

P_ATTRIBUTE20 IN VARCHAR2,

P_PERIOD_OF_INCAPACITY_ID IN NUMBER,

P_SSP1_ISSUED IN VARCHAR2,

P_MATERNITY_ID IN NUMBER,

P_SICKNESS_START_DATE IN DATE,

P_SICKNESS_END_DATE IN DATE,

P_PREGNANCY_RELATED_ILLNESS IN VARCHAR2,

P_REASON_FOR_NOTIFICATION_DELA IN VARCHAR2,

P_ACCEPT_LATE_NOTIFICATION_FLA IN VARCHAR2,

P_LINKED_ABSENCE_ID IN NUMBER,

P_BATCH_ID IN NUMBER,

P_CREATE_ELEMENT_ENTRY IN BOOLEAN,

P_ABS_INFORMATION_CATEGORY IN VARCHAR2,

P_ABS_INFORMATION1 IN VARCHAR2,

...       – Please include all the abs_information1 to abs_information30

P_ABS_INFORMATION30 IN VARCHAR2,

P_ABSENCE_CASE_ID IN NUMBER) IS

L_ABSENCE_TYPE VARCHAR2 (500) := NULL;

L_ASSIGNMENT_ID NUMBER;

L_ABSENCE_START_DATE DATE := NVL (P_DATE_START, P_DATE_PROJECTED_START);

L_ABSENCE_END_DATE DATE := NVL (P_DATE_END, P_DATE_PROJECTED_END);

L_ABSENCE_FUTURE_ST_DATE DATE;

L_ABSENCE_FUTURE_END_DATE DATE;

BEGIN

SELECT NAME

INTO L_ABSENCE_TYPE

FROM PER_ABSENCE_ATTENDANCE_TYPES

WHERE ABSENCE_ATTENDANCE_TYPE_ID = P_ABSENCE_ATTENDANCE_TYPE_ID

AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID;

IF UPPER(TRIM(L_ABSENCE_TYPE)) = ‘ANNUAL LEAVE’ THEN

    IF L_ABSENCE_END_DATE – L_ABSENCE_START_DATE > 30 THEN

        HR_UTILITY.SET_MESSAGE (800, ‘LSG_ANN_LEAVE_GREATER_THAN_30’);

        HR_UTILITY.RAISE_ERROR;

    END IF;

END IF;

END

END XXCUS_CREATE_ABS;

END XXCUS_USERHOOK_PKG;

Hook Registration Script:
Using an API the custom logic will be registered against user hook. The API_HOOK_ID identified in the above query is passed as the parameter (p_api_hook_id) to the API.

DECLARE

L_API_HOOK_ID NUMBER:= 3840;

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 => ‘XXCUS_USERHOOK_PKG’,

P_CALL_PROCEDURE => ‘XXCUS_CREATE_ABS’,

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 ;

Hook Activation Script:
Run pre-processor script (PER_TOP/admin/sql/hrahkone.sql) with module name as parameter.

Alternatively, using the below script you can trigger the pre-processor

DECLARE

L_API_MODULE_ID NUMBER := 1731; –VALUE DERIVED FROM ABOVE QUERY

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;

Below query is used to check the reference of the custom package/procedure in table HR_API_HOOK_CALLS.

Eg: SELECT * FROM HR_API_HOOK_CALLS WHERE api_hook_id = 3840;

Other Scenarios:

Below are other scenarios where we want to put extra logic to add extra business rules.

1. Validate Data in EIT & SIT before or after insertion either through self service or core HR

2. Validating particular customer data: Eg: you could limit grade step promotions to a maximum of one step.

3. Maintaining additional data in your own user defined tables

4. Detecting a particular business event. Eg: The event of an employee termination could be made to send a message to the security database to disable the employee’s security pass.