If using the Absence form, you will get a warning message if an employee takes more hours than are accrued - but it's a soft error, and you can just click OK to go through it.
To prohibit the accrual plan from going negative, there are two distinct options: A. If you are using OTL, you can alter OTL behavior B. If you are not using OTL, you will alter the absence API behavior
Option A: If you are using Oracle Time and Labor, you can setup Time Entry Rules that prohibit the accrual plan from going negative. You have to use the US OTL Application Developer Responsibility. Navigation Path: Time Entry Rules -> Define Time Entry Rules.
Option B:
If you are not using OTL, you will alter the absence API behavior using API user hooks.
a) The API that is being used: HR_PERSON_ABSENCE_API
b) If preventing the PTO balance from going negative, the user hook will have to check the boolean parameter called 'p_exceeds_pto_entit_warning'. If this is set to TRUE then raise an error message.
Implementing User Hooks:
1. Choose the API you wish to hook some extra logic to.
SELECT AHK.API_HOOK_ID,
AHK.HOOK_PACKAGE,
AHK.HOOK_PROCEDURE
FROM HR_API_HOOKS AHK,
HR_API_MODULES AHM
WHERE
AHM.MODULE_NAME LIKE 'CREATE_PERSON_ABSENCE'
AND AHM.API_MODULE_TYPE = 'BP' AND
AHK.API_HOOK_TYPE = 'AP'
AND
AHK.API_MODULE_ID=AHM.API_MODULE_ID;
SELECT AHK.API_HOOK_ID,
AHK.HOOK_PACKAGE,
AHK.HOOK_PROCEDURE
FROM HR_API_HOOKS AHK,
HR_API_MODULES AHM
WHERE
AHM.MODULE_NAME LIKE 'CREATE_PERSON_ABSENCE'
AND AHM.API_MODULE_TYPE = 'BP' AND
AHK.API_HOOK_TYPE = 'AP'
AND
AHK.API_MODULE_ID=AHM.API_MODULE_ID;
it returns API_HOOK_ID, HOOK_PACKAGE, HOOK_PROCEDURE values
2. Write the PL/SQL procedure that you wish to be called by the hook.
CREATE OR REPLACE PACKAGE APL_NEG_BAL_CHECK AS
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN);
END APL_NEG_BAL_CHECK;
CREATE OR REPLACE Package Body APL_NEG_BAL_CHECK as
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN)
IS
CURSOR CSR_GET_ABS_CAT IS
SELECT ABSENCE_CATEGORY
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE ABSENCE_ATTENDANCE_TYPE_ID = P_ABSENCE_ATTENDANCE_TYPE_ID;
L_ABS_CATEGORY VARCHAR2(20);
BEGIN
--Get Absence Category
OPEN CSR_GET_ABS_CAT;
FETCH CSR_GET_ABS_CAT INTO L_ABS_CATEGORY;
CLOSE CSR_GET_ABS_CAT;
IF (L_ABS_CATEGORY ='H') AND (P_EXCEEDS_PTO_ENTIT_WARNING = TRUE) THEN
fnd_message.set_name('PER','HR_LOA_EMP_NOT_ENTITLED');
fnd_message.raise_error;
END IF;
END APL_NEG_ACR_BAL_CHK;
END APL_NEG_BAL_CHECK;
CREATE OR REPLACE PACKAGE APL_NEG_BAL_CHECK AS
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN);
END APL_NEG_BAL_CHECK;
CREATE OR REPLACE Package Body APL_NEG_BAL_CHECK as
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN)
IS
CURSOR CSR_GET_ABS_CAT IS
SELECT ABSENCE_CATEGORY
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE ABSENCE_ATTENDANCE_TYPE_ID = P_ABSENCE_ATTENDANCE_TYPE_ID;
L_ABS_CATEGORY VARCHAR2(20);
BEGIN
--Get Absence Category
OPEN CSR_GET_ABS_CAT;
FETCH CSR_GET_ABS_CAT INTO L_ABS_CATEGORY;
CLOSE CSR_GET_ABS_CAT;
IF (L_ABS_CATEGORY ='H') AND (P_EXCEEDS_PTO_ENTIT_WARNING = TRUE) THEN
fnd_message.set_name('PER','HR_LOA_EMP_NOT_ENTITLED');
fnd_message.raise_error;
END IF;
END APL_NEG_ACR_BAL_CHK;
END APL_NEG_BAL_CHECK;
3. Register or associate the procedure you have written
Pass the input values like HOOK_ID from Step 1
declare
l_api_hook_call_id number;
l_object_version_number number;
begin
hr_api_hook_call_api.create_api_hook_call
(p_validate => false,
p_effective_date => to_date('01-JUL-1999','DD-MON-YYYY'),
p_api_hook_id => 3870,
p_api_hook_call_type => 'PP',
p_sequence => 3000,
p_enabled_flag => 'Y',
p_call_package => 'APL_NEG_BAL_CHECK',
p_call_procedure => 'APL_NEG_ACR_BAL_CHK',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
end;
check is registration successful or not , run the below script
SELECT * FROM HR_API_HOOK_CALLS WHERE CALL_PACKAGE='NEG_BAL_CHECK'
4. Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook.
To run the pre-processor run one of the following commands:
cd $PER_TOP/admin/sql
Log into SQLPLUS as the APPS user
SQL> @hrahkall.sql
If it successful below lines will be added to the HR_PERSON_ABSENCE_BK1 API.
if hr_api.call_cus_hooks then
NEG_BAL_CHECK.NEG_ACR_BAL_CHK
(P_EXCEEDS_PTO_ENTIT_WARNING => P_EXCEEDS_PTO_ENTIT_WARNING
);end if;
5. Now you try to apply leave, the Absence page will not allowed you for next page if Accrual balance is Zero.
