User hook Create, Update and Delete api scripts.

1.
Finding user hook for SIT before insert:

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 '%CREATE_SIT%'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id

API_HOOK_ID = 2879
API_MODULE_ID = 1279

2.  Create User hook:

DECLARE
L_API_HOOK_ID NUMBER:= 2879;
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 => 'XXFUJ_CREATE_SIT_A_UHPKG',
P_CALL_PROCEDURE => 'XXFUJ_CREATE_SIT_LEV_VAL',
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. COMMIT

SELECT API_HOOK_CALL_ID,API_HOOK_ID,
OBJECT_VERSION_NUMBER,
CALL_PACKAGE,
CALL_PROCEDURE,
ENABLED_FLAG,
STATUS
FROM HR_API_HOOK_CALLS
--WHERE api_hook_id = 2878
--WHERE API_HOOK_CALL_ID = 1263
WHERE CALL_PACKAGE LIKE '%FUJ%'

commit

4. runnig the script
declare
l_api_module_id number := 1279; --Value 1731 is derived from Step 1 above using following 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;

5. COMMIT

6. to delete the user hook:

BEGIN
Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,
p_api_hook_call_id => 1274,
p_object_version_number =>2
);
DBMS_OUTPUT.PUT_LINE('deleted Successfully');
END;

7. COMMIT

8. to update the user hook:

declare
p_object_version_number number;
BEGIN
Hr_Api_Hook_Call_Api.update_api_hook_call ( p_validate  =>TRUE,
   p_effective_date               => to_date('01-jan-2004'),
   p_api_hook_call_id             =>1270,
   --p_sequence                     in     number    default hr_api.g_number,
   --p_enabled_flag                 in     varchar2  default hr_api.g_varchar2,
   --p_call_package                 in     varchar2  default hr_api.g_varchar2,
   --p_call_procedure               in     varchar2  default hr_api.g_varchar2,
   p_object_version_number      =>p_object_version_number
);
DBMS_OUTPUT.PUT_LINE('updated Successfully');
END;

9. commit

SELECT ANALYSIS_CRITERIA_ID,
ID_FLEX_NUM,
SEGMENT1 Allowance_Name,
SEGMENT2 EFFECTIVE_DATE,
SEGMENT11 AMOUNT
  FROM
PER_ANALYSIS_CRITERIA
WHERE ANALYSIS_CRITERIA_ID = 4949604

SELECT ELEMENT_NAME, ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F_TL
WHERE LANGUAGE = USERENV('LANG')
AND ELEMENT_TYPE_ID IN (593,559,562,561,564,590,589,597)