How to find out which user hook package and procedure have to use for the requirement ?

We have to find out that what user hook package and procedure we have to use to satisfy the requirement.

for that we have to know on which table we have to do modifications, after knowing that
we can run below query to find out the user hook package and procedure.

Here for example I am taking per_pay_proposals table.

select ahk.api_hook_id,
ahk.hook_package,
ahk.hook_procedure,
ahm.API_MODULE_ID
from hr_api_hooks ahk,
hr_api_modules ahm
where (ahm.module_name='PER_PAY_PROPOSALS'
or  ahm.module_name='PER_PAY_PROPOSALS')
and ahm.api_module_type = 'RH'
and ahk.api_hook_type = 'AI'
and ahk.api_module_id=ahm.api_module_id;

How to create a user hook in Oracle HRMS ?

step 1:
=======

select ahk.api_hook_id,
ahk.hook_package,
ahk.hook_procedure,
ahm.API_MODULE_ID
from hr_api_hooks ahk,
hr_api_modules ahm
where (ahm.module_name='PER_PAY_PROPOSALS'
or  ahm.module_name='PER_PAY_PROPOSALS')
and ahm.api_module_type = 'RH'
and ahk.api_hook_type = 'AI'
and ahk.api_module_id=ahm.api_module_id;

step2:
======

create or replace package XX_SS_PKG_HK_SAL is
PROCEDURE XX_HK_SAL(p_pay_proposal_id               in number,
   p_assignment_id                 in number,
   p_business_group_id             in number,
   p_change_date                   in date,
   p_comments                      in varchar2,
   p_next_sal_review_date          in date,
   p_proposal_reason               in varchar2,
   p_proposed_salary_n             in number,
   p_forced_ranking                in number,
   p_date_to    in date,
   p_performance_review_id         in number,
   p_attribute_category            in varchar2,
   p_attribute1                    in varchar2,
   p_attribute2                    in varchar2,
   p_attribute3                    in varchar2,
   p_attribute4                    in varchar2,
   p_attribute5                    in varchar2,
   p_attribute6                    in varchar2,
   p_attribute7                    in varchar2,
   p_attribute8                    in varchar2,
   p_attribute9                    in varchar2,
   p_attribute10                   in varchar2,
   p_attribute11                   in varchar2,
   p_attribute12                   in varchar2,
   p_attribute13                   in varchar2,
   p_attribute14                   in varchar2,
   p_attribute15                   in varchar2,
   p_attribute16                   in varchar2,
   p_attribute17                   in varchar2,
   p_attribute18                   in varchar2,
   p_attribute19                   in varchar2,
   p_attribute20                   in varchar2,
   p_object_version_number         in number,
   p_multiple_components           in varchar2,
   p_approved                      in varchar2,
   p_inv_next_sal_date_warning     in boolean,
   p_proposed_salary_warning    in boolean,
   p_approved_warning              in boolean,
   p_payroll_warning    in boolean);
end XX_SEH_PKG_HK_SAL;

create or replace package body XX_SS_PKG_HK_SAL is
PROCEDURE XX_HK_SAL(p_pay_proposal_id               in number,
   p_assignment_id                 in number,
   p_business_group_id             in number,
   p_change_date                   in date,
   p_comments                      in varchar2,
   p_next_sal_review_date          in date,
   p_proposal_reason               in varchar2,
   p_proposed_salary_n             in number,
   p_forced_ranking                in number,
   p_date_to    in date,
   p_performance_review_id         in number,
   p_attribute_category            in varchar2,
   p_attribute1                    in varchar2,
   p_attribute2                    in varchar2,
   p_attribute3                    in varchar2,
   p_attribute4                    in varchar2,
   p_attribute5                    in varchar2,
   p_attribute6                    in varchar2,
   p_attribute7                    in varchar2,
   p_attribute8                    in varchar2,
   p_attribute9                    in varchar2,
   p_attribute10                   in varchar2,
   p_attribute11                   in varchar2,
   p_attribute12                   in varchar2,
   p_attribute13                   in varchar2,
   p_attribute14                   in varchar2,
   p_attribute15                   in varchar2,
   p_attribute16                   in varchar2,
   p_attribute17                   in varchar2,
   p_attribute18                   in varchar2,
   p_attribute19                   in varchar2,
   p_attribute20                   in varchar2,
   p_object_version_number         in number,
   p_multiple_components           in varchar2,
   p_approved                      in varchar2,
   p_inv_next_sal_date_warning     in boolean,
   p_proposed_salary_warning    in boolean,
   p_approved_warning              in boolean,
   p_payroll_warning    in boolean)
   is
   nl_pay_proposal_id number(20);
   nl_assignment_id number(20);
   lv_pay_proposal_id number(10);
   lv_count number(1);
   begin

   select count(pps.PAY_PROPOSAL_ID)
          into lv_count
   from per_pay_proposals pps
   where pps.PAY_PROPOSAL_ID=p_pay_proposal_id;

   if lv_count=1 then
   update per_pay_proposals pps1 set pps1.PROPOSED_SALARY= ROUND(pps1.PROPOSED_SALARY,2)
   where pps1.PAY_PROPOSAL_ID=p_pay_proposal_id;
   commit;
   else
   fnd_file.put_line(fnd_file.log,'Invalid Pay Proposal Id');
   end if;
   end XX_HK_SAL;
   end XX_SEH_PKG_HK_SAL;
   /



   select * from HR_API_HOOK_CALLS;


step 3:
=======


DECLARE
L_API_HOOK_ID NUMBER:= 3035;
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-1950','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_SS_PKG_HK_SAL',
P_CALL_PROCEDURE => 'XX_HK_SAL',
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;
/




select * from HR_API_HOOK_CALLS where call_package='XX_SS_PKG_HK_SAL';

select * from HR_API_HOOK_CALLS where call_procedure='XX_HK_SAL';

step 4:
=======


declare
l_api_module_id number := 1416; --Value 1416 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;
/

How to delete a user hook in Oracle apps HRMS?

Before going to delete a user hook we have to find out hook call id and object version number

-> We can find out those values by using the below the query.

SELECT api_hook_call_id,object_version_number
FROM HR_API_HOOK_CALLS
WHERE call_package = 'XX_SS_PKG_HK_SAL'
AND call_procedure = UPPER('XX_HK_SAL');

After finding the api hook call id and OVN

Run the following program

BEGIN
Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,
p_api_hook_call_id => 1539,
p_object_version_number =>5
);
DBMS_OUTPUT.PUT_LINE('deleted Successfully');
commit;
END;
/