1. to Search userhook for EIT.
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_PERSON_EXTRA%'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id
2759 API_HOOK_ID
1226 API_MODULE_ID
2. run the below script to add userhook.
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 => 'XXFUJ_SS_USERHOOK', --package script below
p_call_procedure => 'XXCREATE_PERSON_EXTRA_INFO_B', --poackage proecudre
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
4. check status with below query
SELECT *
FROM HR_API_HOOK_CALLS
WHERE api_hook_id = 2759;
STATUS = 'N'
5. run the pre-processor or below script to complete.
declare
l_api_module_id number := 1226; --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;
6. COMMIT
7. recheck the status should be 'V'
8.
to delete eit userhook:
begin
hr_api_hook_call_api.delete_api_hook_call
(p_validate => FALSE,
p_api_hook_call_id => 1249,
p_object_version_number => 2
);
--
end;
script for package and procedure..................
CREATE OR REPLACE package APPS.XXFUJ_SS_USERHOOK is
-- Author : Ammar Galant
-- Created : 8/8/2017
-- Purpose : Self Service Userhooks
procedure XXCREATE_PERSON_EXTRA_INFO_B(P_PERSON_ID in NUMBER,
P_INFORMATION_TYPE in VARCHAR2,
P_PEI_ATTRIBUTE_CATEGORY in VARCHAR2,
P_PEI_ATTRIBUTE1 in VARCHAR2,
P_PEI_ATTRIBUTE2 in VARCHAR2,
P_PEI_ATTRIBUTE3 in VARCHAR2,
P_PEI_ATTRIBUTE4 in VARCHAR2,
P_PEI_ATTRIBUTE5 in VARCHAR2,
P_PEI_ATTRIBUTE6 in VARCHAR2,
P_PEI_ATTRIBUTE7 in VARCHAR2,
P_PEI_ATTRIBUTE8 in VARCHAR2,
P_PEI_ATTRIBUTE9 in VARCHAR2,
P_PEI_ATTRIBUTE10 in VARCHAR2,
P_PEI_ATTRIBUTE11 in VARCHAR2,
P_PEI_ATTRIBUTE12 in VARCHAR2,
P_PEI_ATTRIBUTE13 in VARCHAR2,
P_PEI_ATTRIBUTE14 in VARCHAR2,
P_PEI_ATTRIBUTE15 in VARCHAR2,
P_PEI_ATTRIBUTE16 in VARCHAR2,
P_PEI_ATTRIBUTE17 in VARCHAR2,
P_PEI_ATTRIBUTE18 in VARCHAR2,
P_PEI_ATTRIBUTE19 in VARCHAR2,
P_PEI_ATTRIBUTE20 in VARCHAR2,
P_PEI_INFORMATION_CATEGORY in VARCHAR2,
P_PEI_INFORMATION1 in VARCHAR2,
P_PEI_INFORMATION2 in VARCHAR2,
P_PEI_INFORMATION3 in VARCHAR2,
P_PEI_INFORMATION4 in VARCHAR2,
P_PEI_INFORMATION5 in VARCHAR2,
P_PEI_INFORMATION6 in VARCHAR2,
P_PEI_INFORMATION7 in VARCHAR2,
P_PEI_INFORMATION8 in VARCHAR2,
P_PEI_INFORMATION9 in VARCHAR2,
P_PEI_INFORMATION10 in VARCHAR2,
P_PEI_INFORMATION11 in VARCHAR2,
P_PEI_INFORMATION12 in VARCHAR2,
P_PEI_INFORMATION13 in VARCHAR2,
P_PEI_INFORMATION14 in VARCHAR2,
P_PEI_INFORMATION15 in VARCHAR2,
P_PEI_INFORMATION16 in VARCHAR2,
P_PEI_INFORMATION17 in VARCHAR2,
P_PEI_INFORMATION18 in VARCHAR2,
P_PEI_INFORMATION19 in VARCHAR2,
P_PEI_INFORMATION20 in VARCHAR2,
P_PEI_INFORMATION21 in VARCHAR2,
P_PEI_INFORMATION22 in VARCHAR2,
P_PEI_INFORMATION23 in VARCHAR2,
P_PEI_INFORMATION24 in VARCHAR2,
P_PEI_INFORMATION25 in VARCHAR2,
P_PEI_INFORMATION26 in VARCHAR2,
P_PEI_INFORMATION27 in VARCHAR2,
P_PEI_INFORMATION28 in VARCHAR2,
P_PEI_INFORMATION29 in VARCHAR2,
P_PEI_INFORMATION30 in VARCHAR2);
end XXFUJ_SS_USERHOOK;
/************************************************* Package Body **********************************************************/
CREATE OR REPLACE PACKAGE BODY APPS.XXFUJ_SS_USERHOOK
IS
PROCEDURE XXCREATE_PERSON_EXTRA_INFO_B (
P_PERSON_ID IN NUMBER,
P_INFORMATION_TYPE IN VARCHAR2,
P_PEI_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_PEI_ATTRIBUTE1 IN VARCHAR2,
P_PEI_ATTRIBUTE2 IN VARCHAR2,
P_PEI_ATTRIBUTE3 IN VARCHAR2,
P_PEI_ATTRIBUTE4 IN VARCHAR2,
P_PEI_ATTRIBUTE5 IN VARCHAR2,
P_PEI_ATTRIBUTE6 IN VARCHAR2,
P_PEI_ATTRIBUTE7 IN VARCHAR2,
P_PEI_ATTRIBUTE8 IN VARCHAR2,
P_PEI_ATTRIBUTE9 IN VARCHAR2,
P_PEI_ATTRIBUTE10 IN VARCHAR2,
P_PEI_ATTRIBUTE11 IN VARCHAR2,
P_PEI_ATTRIBUTE12 IN VARCHAR2,
P_PEI_ATTRIBUTE13 IN VARCHAR2,
P_PEI_ATTRIBUTE14 IN VARCHAR2,
P_PEI_ATTRIBUTE15 IN VARCHAR2,
P_PEI_ATTRIBUTE16 IN VARCHAR2,
P_PEI_ATTRIBUTE17 IN VARCHAR2,
P_PEI_ATTRIBUTE18 IN VARCHAR2,
P_PEI_ATTRIBUTE19 IN VARCHAR2,
P_PEI_ATTRIBUTE20 IN VARCHAR2,
P_PEI_INFORMATION_CATEGORY IN VARCHAR2,
P_PEI_INFORMATION1 IN VARCHAR2,
P_PEI_INFORMATION2 IN VARCHAR2,
P_PEI_INFORMATION3 IN VARCHAR2,
P_PEI_INFORMATION4 IN VARCHAR2,
P_PEI_INFORMATION5 IN VARCHAR2,
P_PEI_INFORMATION6 IN VARCHAR2,
P_PEI_INFORMATION7 IN VARCHAR2,
P_PEI_INFORMATION8 IN VARCHAR2,
P_PEI_INFORMATION9 IN VARCHAR2,
P_PEI_INFORMATION10 IN VARCHAR2,
P_PEI_INFORMATION11 IN VARCHAR2,
P_PEI_INFORMATION12 IN VARCHAR2,
P_PEI_INFORMATION13 IN VARCHAR2,
P_PEI_INFORMATION14 IN VARCHAR2,
P_PEI_INFORMATION15 IN VARCHAR2,
P_PEI_INFORMATION16 IN VARCHAR2,
P_PEI_INFORMATION17 IN VARCHAR2,
P_PEI_INFORMATION18 IN VARCHAR2,
P_PEI_INFORMATION19 IN VARCHAR2,
P_PEI_INFORMATION20 IN VARCHAR2,
P_PEI_INFORMATION21 IN VARCHAR2,
P_PEI_INFORMATION22 IN VARCHAR2,
P_PEI_INFORMATION23 IN VARCHAR2,
P_PEI_INFORMATION24 IN VARCHAR2,
P_PEI_INFORMATION25 IN VARCHAR2,
P_PEI_INFORMATION26 IN VARCHAR2,
P_PEI_INFORMATION27 IN VARCHAR2,
P_PEI_INFORMATION28 IN VARCHAR2,
P_PEI_INFORMATION29 IN VARCHAR2,
P_PEI_INFORMATION30 IN VARCHAR2)
IS
LN_LEAVE_START DATE;
BEGIN
IF P_INFORMATION_TYPE = 'FUJ_UPDATE_DELETE_LEAVE' THEN
select date_start
INTO LN_LEAVE_START
from per_absence_attendances
where ABSENCE_ATTENDANCE_ID = to_number(P_PEI_INFORMATION2);
IF TRUNC(sysdate) >= LN_LEAVE_START AND TRUNC(SYSDATE) >= TO_DATE('11-AUG-2017')
THEN
hr_utility.set_message (800, 'FUJ_DELETE_LEAVE_VALID');
hr_utility.raise_error;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('hi');
END XXCREATE_PERSON_EXTRA_INFO_B;
END XXFUJ_SS_USERHOOK;
/
No comments:
Post a Comment