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.

                                    eAM  - Asset Number Conversion

Asset Number:

An asset number uniquely identifies each asset.

Possible Validations:

• Asset Number Mandatory.
• Asset Serial Number Mandatory.
• Asset Group Mandatory.
• Asset Category Mandatory.
• Owning Department Mandatory.
• Criticality Code Mandatory.
• Area Mandatory.
• Wip Accounting Class Mandatory.

Code for reference:

PROCEDURE XXX_validate

   IS

      CURSOR cur_main

      IS

         SELECT *

           FROM xxx_stage —-Select all the records to be processed

         

      l_organization_id     NUMBER;

      l_inventory_item_id   NUMBER;

      l_asset_count         NUMBER;

      l_category_id         NUMBER;

      l_department_id       NUMBER;

      l_area_id             NUMBER;

      l_criticality_code    VARCHAR2 (10);

      l_class_code          VARCHAR2 (100);

      l_user_id             NUMBER;

      lc_cat_seg            VARCHAR2 (245);

   BEGIN

     

      FOR i IN cur_main

      LOOP

     ---Validate Category Set

         BEGIN

            SELECT category_concat_segs

              INTO lc_cat_seg

              FROM mtl_item_categories_v mic, mtl_system_items_b msi

             WHERE mic.category_set_name = :set_name                        

             AND mic.inventory_item_id = msi.inventory_item_id

               AND mic.organization_id = msi.organization_id

               AND msi.segment1 = i.asset_group

               AND msi.organization_id =i.organization_code);

                  END;

--To Validate Asset Group

        

            BEGIN

               SELECT inventory_item_id

                 INTO l_inventory_item_id

                 FROM mtl_system_items_b

                WHERE segment1 = i.asset_group

                  AND organization_id = l_organization_id;

            END;

            --Validate Asset category

            IF i.asset_category IS NOT NULL

            THEN

               BEGIN

                SELECT category_id

                 INTO l_category_id

                FROM mtl_categories_v

             WHERE UPPER (category_concat_segs)= (i.asset_category)

              AND structure_name = i.name            

              AND enabled_flag = 'Y';

               END;

            END IF;

            --Validate owning department

            BEGIN

               SELECT department_id

                 INTO l_department_id

                 FROM bom_departments

                WHERE UPPER (department_code) = (i.owning_department)

                 AND organization_id = l_organization_id

                  AND disable_date IS NULL;

           

            END;

            IF i.criticality IS NOT NULL

            THEN

               BEGIN

                  SELECT lookup_code

                    INTO l_criticality_code

                    FROM fnd_lookup_values

                   WHERE lookup_type = 'MTL_EAM_ASSET_CRITICALITY'

                     AND meaning = i.criticality

                     AND enabled_flag = 'Y';

               End;

            END IF;

            --Validate Area

            IF i.area IS NOT NULL

            THEN

               BEGIN

                  SELECT location_id

                    INTO l_area_id

                    FROM mtl_eam_locations

                   WHERE location_codes = i.area

                     AND organization_id = l_organization_id

                     AND end_date IS NULL;

              

               END;

            END IF;

            --Validate WIP Accounting Classe

            BEGIN

               SELECT class_code

                 INTO l_class_code

                 FROM wip_accounting_classes

                WHERE class_code = i.wip_accounting_class

                  AND organization_id = l_organization_id;

                              END;

         IF l_error_message IS NULL

           -- UPDATE  stage table with process flag as ‘V’

         END IF;

      END LOOP;

      COMMIT;

   END validate_asset_data;

PROCEDURE XXX_import (

   errbuff      OUT      VARCHAR2,

   retcode      OUT      NUMBER,

   p_batch_no   IN       NUMBER

)

IS

   CURSOR cur_main

   IS

      SELECT *

        FROM XXX_STG

       WHERE process_flag = 'V' AND batch_no = p_batch_no;

   l_error     VARCHAR2 (1000);

   l_user_id   NUMBER;

BEGIN

    FOR i IN cur_main

   LOOP

      l_error := NULL;

  BEGIN

   INSERT INTO mtl_eam_asset_num_interface

   (inventory_item_id, serial_number, last_update_date,

   last_updated_by, creation_date, created_by, descriptive_text,

   wip_accounting_class_code, maintainable_flag,

   owning_department_id, fa_asset_id,

   eam_location_id, asset_criticality_code,

   category_id, interface_header_id, batch_id, organization_code,

   fa_asset_number, location_codes, process_flag, import_mode,                       import_scope, owning_department_code, asset_criticality_id,                       instance_number, operational_log_flag)

   VALUES (i.inventory_item_id, i.asset_serial_number, SYSDATE,

   l_user_id, SYSDATE, l_user_id, i.asset_description,

   i.class_code, 'Y', i.department_id, i.category_id,

   mtl_eam_asset_num_interface_s.NEXTVAL, i.organization_code,

   i.finance_asset_number, i.area, 'P', 1,

   NULL, i.criticality_code, i.asset_number, 'Y'

   );

END;

END LOOP;

END;

Run the Standard Program  “ Import Asset  Number”  .

Base Table :

• MTL_SERIAL_NUMBERS
• CSI_ITEM_INSTANCES

Sample Query to get the  Asset Number Details:

SELECT   (SELECT organization_code

            FROM org_organization_definitions

           WHERE organization_id = last_vld_organization_id) "Inventory Org",

         (SELECT DISTINCT segment1

                     FROM mtl_system_items_b

                    WHERE inventory_item_id =

                                           c.inventory_item_id)

                                                               "Asset Groups",

         instance_number "Asset Number",

         instance_description "Asset Number Description",

         c.serial_number "Asset Serial Number",

         (SELECT department_code

            FROM bom_departments

           WHERE department_id =

                       (SELECT owning_department_id

                          FROM eam_org_maint_defaults

                         WHERE object_id = c.instance_id))

                                                          "owning department",

         (SELECT meaning

            FROM fnd_lookup_values

           WHERE lookup_type = 'MTL_EAM_ASSET_CRITICALITY'

             AND enabled_flag = 'Y'

             AND lookup_code = c.asset_criticality_code) "criticality",

         (SELECT accounting_class_code

            FROM eam_org_maint_defaults

           WHERE object_id = c.instance_id) "accounting_class_code",

         (SELECT location_codes

            FROM mtl_eam_locations

           WHERE location_id = (SELECT area_id

                                  FROM eam_org_maint_defaults

                                 WHERE object_id = c.instance_id)) "area"

    FROM csi_item_instances c, mtl_serial_numbers msn

   WHERE c.serial_number = msn.serial_number

     AND c.inventory_item_id = msn.inventory_item_id

ORDER BY instance_number