HRMS FAST FORMULA REQUIREMENT
Requirement:
In HRMS responsibility
Employee Self Service Indiaà Absence ManagementàCreate absence.
Validations:
1.To ensure employee do not enter Unpaid leave with start date and end date across two months.
2. To ensure employee do not enter the unpaid leave with start date and end date acroos weekends.
(Business request:
To block employee from creating an Unpaid leave request with end date (as Friday) if leave request for Monday (end date + 3) is with "pending for approval" status AND
To block employee from creating an Unpaid leave request with start date (as Monday) if leave request for Friday (start date - 3) is with "pending for approval" status.)
3. To ensure employee do not enter duplicate leave of leaves Pending for approval or Return for correction.
4. To ensure employee do not enter duplicate leave of existing approved leave.
Solution:
First we have to develop a functions for these validations and add a function into predefined package for that fast formula.
1. Function for unpaid leave with start date and end date across two months.
CREATE OR REPLACE FUNCTION PFC_IN_LOP_CHECK(P_EMPLOYEE_NUMBER VARCHAR2,
P_START_DATE DATE,
P_END_DATE DATE) return number is
L_COUNT NUMBER := 0;
begin
IF (SUBSTR(TO_CHAR(P_START_DATE),4,3)=SUBSTR(TO_CHAR(P_END_DATE),4,3))
then
l_count:=0;
else
l_count:=1;
end if;
RETURN L_COUNT;
end;
2. Function for unpaid leave with start date and end date across the weekends.
CREATE OR REPLACE FUNCTION PFC_UPL_ACROSS_WEEKEND (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER
IS
l_count NUMBER;
p_date_end_day VARCHAR2 (150);
p_date_start_day VARCHAR2 (150);
BEGIN
BEGIN
SELECT decode(TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),'MONDAY','MONDAY',
TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),'MONDAY','MONDAY') into p_date_end_day
FROM hr_api_transactions ht, hr_api_transaction_steps hats, per_all_people_f papf
WHERE
(to_char(P_START_DATE+3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2
OR to_char(P_END_DATE+3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2)
and
PAPF.PERSON_ID=ht.CREATOR_PERSON_ID
and ht.transaction_id = hats.transaction_id
AND hats.information6 = 'Unpaid Leave'
AND PAPF.PERSON_ID=hats.CREATOR_PERSON_ID
AND ht.status = 'Y'
AND( REPLACE (TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'MONDAY'
OR
REPLACE (TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'MONDAY')
AND PAPF.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER;
/* SELECT TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY') into p_date_end_day
FROM hr_api_transactions ht, hr_api_transaction_steps hats, per_all_people_f papf
WHERE
hats.information2 = (TO_CHAR (p_end_date + 3, 'YYYY-MM-DD'))
and PAPF.PERSON_ID=ht.CREATOR_PERSON_ID
and ht.transaction_id = hats.transaction_id
AND hats.information6 = 'Unpaid Leave'
AND PAPF.PERSON_ID=hats.CREATOR_PERSON_ID
AND ht.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'MONDAY'
AND PAPF.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER;*/
/*SELECT TO_CHAR (TO_DATE (hats1.information2, 'YYYY/MM/DD'), 'DAY')
INTO p_date_start_day
FROM hr_api_transaction_steps hats1,
hr_api_transactions hat, --hr_lookups hrl1,
per_all_people_f papf1
WHERE
(to_char(P_START_DATE+3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2
OR to_char(P_END_DATE+3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2)
AND papf1.employee_number = p_employee_number
AND
hats1.creator_person_id = papf1.person_id
AND hats1.transaction_id = hat.transaction_id
AND hats1.information6 = 'Unpaid Leave'
AND hat.creator_person_id = papf1.person_id
AND hat.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats1.information1,
'YYYY/MM/DD'
),
'DAY'
),
' ',
''
) = 'MONDAY';*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found In p_end_date');
DBMS_OUTPUT.put_line ('p_date_end_day:' || p_date_end_day);
DBMS_OUTPUT.put_line ('p_emp' || p_employee_number);
DBMS_OUTPUT.put_line ('p_end_date' || p_end_date);
END;
BEGIN
SELECT decode(TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),'FRIDAY','FRIDAY',
TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),'FRIDAY','FRIDAY') INTO p_date_start_day
FROM hr_api_transactions ht, hr_api_transaction_steps hats, per_all_people_f papf
WHERE
(to_char(P_START_DATE-3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2
OR to_char(P_END_DATE-3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2)
and
PAPF.PERSON_ID=ht.CREATOR_PERSON_ID
and ht.transaction_id = hats.transaction_id
AND hats.information6 = 'Unpaid Leave'
AND PAPF.PERSON_ID=hats.CREATOR_PERSON_ID
AND ht.status = 'Y'
AND( REPLACE (TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'FRIDAY'
OR
REPLACE (TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'FRIDAY')
AND PAPF.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER;
/*SELECT TO_CHAR (TO_DATE (hats1.information1, 'YYYY/MM/DD'), 'DAY')
INTO p_date_start_day
FROM hr_api_transaction_steps hats1,
hr_api_transactions hat, --hr_lookups hrl1,
per_all_people_f papf1
WHERE
hats1.information1 =(TO_CHAR (p_start_date - 3, 'YYYY-MM-DD'))
AND
hats1.creator_person_id = papf1.person_id
AND hats1.transaction_id = hat.transaction_id
AND hats1.information6 = 'Unpaid Leave'
AND hat.creator_person_id = papf1.person_id
AND hat.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats1.information1,
'YYYY/MM/DD'
),
'DAY'
),
' ',
''
) = 'FRIDAY'
AND papf1.employee_number = p_employee_number;*/
/*SELECT TO_CHAR (TO_DATE (hats1.information1, 'YYYY/MM/DD'), 'DAY')
INTO p_date_start_day
FROM hr_api_transaction_steps hats1,
hr_api_transactions hat, --hr_lookups hrl1,
per_all_people_f papf1
WHERE
(to_char(P_START_DATE-3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2
OR to_char(P_END_DATE-3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2)
AND papf1.employee_number = p_employee_number
AND
hats1.creator_person_id = papf1.person_id
AND hats1.transaction_id = hat.transaction_id
AND hats1.information6 = 'Unpaid Leave'
AND hat.creator_person_id = papf1.person_id
AND hat.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats1.information1,
'YYYY/MM/DD'
),
'DAY'
),
' ',
''
) = 'FRIDAY';*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found In p_start_date');
DBMS_OUTPUT.put_line ('p_date_start_day:' || p_date_start_day);
DBMS_OUTPUT.put_line ('p_emp' || p_employee_number);
DBMS_OUTPUT.put_line ('p_start_date' || p_start_date);
END;
IF p_date_end_day = 'MONDAY' OR p_date_start_day = 'FRIDAY'
THEN
l_count := 1;
ELSE
l_count := 0;
END IF;
RETURN (l_count);
END;
3. Function for duplicate leave of leaves Pending for approval or Return for correction.
CREATE OR REPLACE FUNCTION pfc_check_pending_leave (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER
IS
l_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM PER_ALL_PEOPLE_F PERSON,
APPS.HR_API_TRANSACTION_STEPS HATS2, --LEAVE PENDING FOR APPROVAL,
APPS.HR_API_TRANSACTIONS HAT2 --LEAVE PENDING FOR APPROVAL,
WHERE PERSON.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER
AND HAT2.SELECTED_PERSON_ID=PERSON.PERSON_ID
AND HAT2.TRANSACTION_ID = HATS2.TRANSACTION_ID
AND TRUNC (SYSDATE) BETWEEN PERSON.EFFECTIVE_START_DATE AND PERSON.EFFECTIVE_END_DATE
AND HAT2.TRANSACTION_REF_TABLE = 'PER_ABSENCE_ATTENDANCES'
AND HAT2.STATUS IN ('Y', 'RI') --- Y is for Pending for approval status and RI is for Return for correction
AND (TO_DATE(HATS2.INFORMATION1,'YYYY-MM-DD') BETWEEN P_START_DATE AND P_END_DATE
OR TO_DATE(HATS2.INFORMATION2,'YYYY-MM-DD') BETWEEN P_START_DATE AND P_END_DATE);
/*FROM
per_all_people_f person,
apps.hr_api_transaction_steps hats2, --leave pending for approval,
apps.hr_api_transactions hat2 --leave pending for approval,
WHERE person.employee_number = p_employee_number
AND hat2.selected_person_id=person.person_id
AND hat2.transaction_id = hats2.transaction_id
AND TRUNC (SYSDATE) BETWEEN person.effective_start_date AND person.effective_end_date
AND hat2.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
AND hat2.status in ('Y', 'RI') --- Y is for Pending for approval status and RI is for Return for correction
and (TO_CHAR(TO_DATE(p_start_date),'YYYY-MM-DD') BETWEEN (hatS2.information1) AND (hatS2.information2)
or TO_CHAR(TO_DATE(p_END_date),'YYYY-MM-DD') BETWEEN (hatS2.information1) AND (hatS2.information2) ) ;*/
RETURN l_count;
END;
4. Function for employee do not enter duplicate leave of existing approved leave.
CREATE OR REPLACE FUNCTION pfc_in_check_approved_overlap (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
--P_TRANSACTION_REF_ID NUMBER
)
RETURN NUMBER
IS
l_count NUMBER;
l_abs_id NUMBER;
l_trans_ref NUMBER;
l_start_date DATE;
l_end_date DATE;
BEGIN
BEGIN
SELECT absence_attendance_id, date_start, date_end
INTO l_abs_id, l_start_date, l_end_date
FROM per_absence_attendances paa
,per_all_people_f papf
WHERE paa.person_id = papf.person_id
AND papf.employee_number = p_employee_number
AND (date_start between p_start_date and p_end_Date OR date_end between p_start_date and p_end_date);
EXCEPTION WHEN OTHERS THEN
l_abs_id := 0;
END;
BEGIN
select hat.transaction_ref_id
INTO l_trans_ref
from hr_api_transactions hat
,hr_api_transaction_steps hats
where hat.transaction_id = hats.transaction_id
and FND_DATE.CANONICAL_TO_DATE(hats.information1) = l_start_date
and FND_DATE.CANONICAL_TO_DATE(hats.information2) = l_end_date
and hat.status = 'W';
EXCEPTION WHEN OTHERS THEN
l_trans_ref := 0;
END;
IF l_abs_id = l_trans_ref
THEN l_count := 0;
ELSE l_count := 1;
END IF;
RETURN l_count;
END;
Then the functions added into predefined package spec and body.
Package Name: APPS.pfc_in_leave_utility_pkg
---------------------------------package spec -------------------------------------------------
CREATE OR REPLACE PACKAGE APPS.pfc_in_leave_utility_pkg
AS
FUNCTION pfc_in_check_if_holiday (
p_employee_number VARCHAR2,
p_start_date DATE
)
RETURN NUMBER;
FUNCTION pfc_in_check_effective_holiday (
p_employee_number VARCHAR2,
p_start_date DATE
)
RETURN NUMBER;
FUNCTION pfc_in_check_effective_hdays (
p_employee_number VARCHAR2,
p_end_date DATE
)
RETURN NUMBER;
FUNCTION pfc_in_check_consec_holidays (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER;
FUNCTION pfc_in_check_effective_hbdays (
p_employee_number VARCHAR2,
p_end_date DATE
)
RETURN NUMBER;
FUNCTION pfc_in_check_absence_overlap (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
--p_absence_attendance_id NUMBER
)
RETURN NUMBER;
FUNCTION pfc_in_lop_check (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER;
FUNCTION pfc_upl_across_weekend (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER;
FUNCTION pfc_check_pending_leave (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER;
FUNCTION pfc_in_check_approved_overlap (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
--P_TRANSACTION_REF_ID NUMBER
)
RETURN NUMBER;
END pfc_in_leave_utility_pkg;
/
---------------------------------package body ---------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.PFC_IN_LEAVE_UTILITY_PKG AS
FUNCTION PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER VARCHAR2,
P_START_DATE DATE) RETURN NUMBER IS
CURSOR C_COUNT IS
SELECT 1
FROM HXT_HOLIDAY_DAYS_VL HHDV,
HXT_HOLIDAY_CALENDARS HHC,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_ALL_PEOPLE_F PAPF,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE HHDV.HCL_ID = HHC.ID
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND DECODE(SUBSTR(HHC.NAME,1,((SELECT INSTR(HHC.NAME, '-') FROM dual) - 1)),'Chennai','PESIPL','Mumbai','PEIPL') = SUBSTR(HAOU.NAME,1,INSTR(HAOU.NAME,'-',1,1)-1)
AND SUBSTR(HHC.NAME,((SELECT INSTR(HHC.NAME, '-') FROM dual) + 1),4) = TO_CHAR(P_START_DATE, 'YYYY')
AND TRUNC(HHDV.HOLIDAY_DATE) = TRUNC(P_START_DATE)
AND TRIM(TO_CHAR(HHDV.HOLIDAY_DATE,'DAY')) NOT IN ('SATURDAY','SUNDAY') -- INCLUDED TO TAKE CARE OF HOLIDAYS WHICH FALL ON SATURDAYS AND SUNDAYS
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(P_START_DATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAAF.BUSINESS_GROUP_ID = 81
UNION ALL
SELECT 1 FROM DUAL
WHERE (TRIM(TO_CHAR(P_START_DATE,'DAY')) IN ('SATURDAY','SUNDAY'));
L_COUNT NUMBER := 0;
L_DUMMY C_COUNT%ROWTYPE;
BEGIN
OPEN C_COUNT;
FETCH C_COUNT
INTO L_DUMMY;
IF C_COUNT%FOUND THEN
L_COUNT := L_COUNT + 1;
ELSE
L_COUNT := 0;
END IF;
CLOSE C_COUNT;
RETURN L_COUNT;
END;
FUNCTION PFC_IN_CHECK_EFFECTIVE_HOLIDAY(P_EMPLOYEE_NUMBER VARCHAR2,
P_START_DATE DATE)
RETURN NUMBER IS
holidays NUMBER := 0;
stdate DATE := NULL;
PREVIOUS_DATE_END DATE := NULL;
L_COUNT_PRV NUMBER := 0;
BEGIN
holidays := 0;
PREVIOUS_DATE_END := NULL;
--L_COUNT_PRV :=0;
stdate := P_START_DATE - 1;
WHILE PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, stDate) <> 0 LOOP
IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, stDate) = 1 THEN
holidays := holidays + 1;
END IF;
stDate := stDate - 1;
END LOOP;
BEGIN
SELECT NVL(MAX(PAA.DATE_END), NULL)
INTO PREVIOUS_DATE_END
FROM PER_ABSENCE_ATTENDANCES PAA,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPOS
WHERE ABSENCE_ATTENDANCE_TYPE_ID IN (61)/*(2061, 2063)*/ AND
PAA.PERSON_ID = PAPF.PERSON_ID AND
PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER AND
PAPF.PERSON_ID = PAAF.PERSON_ID AND
NVL(TRUNC(PPOS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE)) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE AND
PPOS.PERSON_ID = PAPF.PERSON_ID AND
PAA.DATE_END < P_START_DATE AND PAA.DATE_END = stDate AND
TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
PAPF.EFFECTIVE_END_DATE AND papf.business_group_id = 81;
END;
IF PREVIOUS_DATE_END IS NOT NULL THEN
IF ((P_START_DATE - HOLIDAYS) <> (PREVIOUS_DATE_END + 1)) THEN
holidays := 0;
/*else holidays := 2;*/
END IF;
END IF;
-------------------------------------------------------------------------------------------------
BEGIN
SELECT COUNT(*)
INTO L_COUNT_PRV
FROM PER_ABSENCE_ATTENDANCES PAA,
per_all_people_f PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAA.PERSON_ID = PAPF.PERSON_ID AND
paa.ABSENCE_ATTENDANCE_TYPE_ID IN (61)/*(2061, 2063)*/ AND
TRUNC(PAA.DATE_END) = TRUNC(PREVIOUS_DATE_END) AND
--AND TRUNC(PAA.DATE_END) = TRUNC(TO_DATE('09-JAN-2009','DD-MON-YYYY'))
PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER AND
PAPF.PERSON_ID = PAAF.PERSON_ID AND
NVL(TRUNC(PPOS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE)) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE AND
PPOS.PERSON_ID = PAPF.PERSON_ID AND
TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND
TRUNC(PAPF.EFFECTIVE_END_DATE) AND
papf.business_group_id = 81;
END;
---------------------------------------------------------------------------------------------
IF (L_COUNT_PRV = 0) THEN
holidays := 0;
END IF;
RETURN(holidays);
END;
/* ***************************************************** */
FUNCTION PFC_IN_CHECK_EFFECTIVE_HDAYS(P_EMPLOYEE_NUMBER VARCHAR2,
P_END_DATE DATE)
RETURN NUMBER IS
holidays NUMBER := 0;
endate DATE := NULL;
PREVIOUS_DATE_START DATE := NULL;
L_COUNT_PRV NUMBER := 0;
BEGIN
holidays := 0;
PREVIOUS_DATE_START := NULL;
--L_COUNT_PRV :=0;
endate := P_END_DATE + 1;
WHILE PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, enDate) <> 0 LOOP
IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, enDate) = 1 THEN
holidays := holidays + 1;
END IF;
enDate := enDate + 1;
END LOOP;
BEGIN
SELECT NVL(MIN(PAA.DATE_START), NULL)
INTO PREVIOUS_DATE_START
FROM PER_ABSENCE_ATTENDANCES PAA,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPOS
WHERE ABSENCE_ATTENDANCE_TYPE_ID IN (61) AND
PAA.PERSON_ID = PAPF.PERSON_ID AND
PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER AND
PAPF.PERSON_ID = PAAF.PERSON_ID AND
NVL(TRUNC(PPOS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE)) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE AND
PPOS.PERSON_ID = PAPF.PERSON_ID AND
PAA.DATE_START > P_END_DATE AND PAA.DATE_START = enDate AND
TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
PAPF.EFFECTIVE_END_DATE AND papf.business_group_id = 81;
END;
IF PREVIOUS_DATE_START IS NOT NULL THEN
IF ((P_END_DATE + HOLIDAYS) <> (PREVIOUS_DATE_START - 1)) THEN
holidays := 0;
/*else holidays := 2;*/
END IF;
END IF;
-------------------------------------------------------------------------------------------------
BEGIN
SELECT COUNT(*)
INTO L_COUNT_PRV
FROM PER_ABSENCE_ATTENDANCES PAA,
per_all_people_f PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAA.PERSON_ID = PAPF.PERSON_ID AND
paa.ABSENCE_ATTENDANCE_TYPE_ID IN (61) AND
TRUNC(PAA.DATE_START) = TRUNC(PREVIOUS_DATE_START) AND
--AND TRUNC(PAA.DATE_END) = TRUNC(TO_DATE('09-JAN-2009','DD-MON-YYYY'))
PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER AND
PAPF.PERSON_ID = PAAF.PERSON_ID AND
NVL(TRUNC(PPOS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE)) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE AND
PPOS.PERSON_ID = PAPF.PERSON_ID AND
TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND
TRUNC(PAPF.EFFECTIVE_END_DATE) AND
papf.business_group_id = 81;
END;
---------------------------------------------------------------------------------------------
IF (L_COUNT_PRV = 0) THEN
holidays := 0;
END IF;
RETURN(holidays);
END;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*******************FUNCTION TO CHECK WHETHER START DATE AND END DATE OF THE LEAVE ARE HOLIDAYS AND TO EXCLUDE CONSECUTIVE HOLIDAYS*************************************************/
FUNCTION PFC_IN_CHECK_CONSEC_HOLIDAYS(P_EMPLOYEE_NUMBER VARCHAR2,
P_START_DATE DATE,
P_END_DATE DATE)
RETURN NUMBER IS
holidays_en_date NUMBER(10);
holidays_st_date NUMBER(10);
duration NUMBER(10);
stdate DATE;
endate DATE;
variable NUMBER(10);
holidays number(10);
l_count number(10);
BEGIN
stdate := P_START_DATE;
endate := P_END_DATE;
duration := (P_END_DATE - P_START_DATE) + 1; -- PER_ACCRUAL_CALC_FUNCTIONS.GET_WORKING_DAYS(P_START_DATE,P_END_DATE); /* COMMENTED SINCE THE WEEKENDS HAVE BEEN INCLUDED IN the CHECK IF HOLIDAY FUNCTION*/
holidays := 0;
variable := 0;
l_count := (P_END_DATE - P_START_DATE) + 1;
-- IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, P_START_DATE) <> 0 THEN
-- IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, P_END_DATE) <> 0 THEN
WHILE l_count > 0 LOOP
IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, stdate) = 1 THEN
holidays := holidays + 1;
END IF;
stdate := stdate + 1;
l_count := l_count - 1;
END LOOP;
/* WHILE PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER,
stDate) <> 0 LOOP
IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, stDate) = 1 THEN
holidays_st_date := holidays_st_date + 1;
END IF;
stDate := stDate + 1;
END LOOP;*/
variable := (duration - holidays);
IF variable < 0 THEN
variable := 0;
END IF;
RETURN variable;
END PFC_IN_CHECK_CONSEC_HOLIDAYS;
/* ***************************************************** */
FUNCTION PFC_IN_CHECK_EFFECTIVE_HBDAYS(P_EMPLOYEE_NUMBER VARCHAR2,
P_END_DATE DATE)
RETURN NUMBER IS
holidays NUMBER := 0;
endate DATE := NULL;
PREVIOUS_DATE_END DATE := NULL;
L_COUNT_PRV NUMBER := 0;
BEGIN
holidays := 0;
PREVIOUS_DATE_END := NULL;
--L_COUNT_PRV :=0;
endate := P_END_DATE - 1;
WHILE PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, enDate) <> 0 LOOP
IF PFC_IN_CHECK_IF_HOLIDAY(P_EMPLOYEE_NUMBER, enDate) = 1 THEN
holidays := holidays + 1;
END IF;
enDate := enDate - 1;
END LOOP;
dbms_output.put_line(enDate);
BEGIN
SELECT NVL(MIN(PAA.DATE_END), NULL)
INTO PREVIOUS_DATE_END
FROM PER_ABSENCE_ATTENDANCES PAA,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPOS
WHERE ABSENCE_ATTENDANCE_TYPE_ID IN (61) AND
PAA.PERSON_ID = PAPF.PERSON_ID AND
PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER AND
PAPF.PERSON_ID = PAAF.PERSON_ID AND
NVL(TRUNC(PPOS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE)) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE AND
PPOS.PERSON_ID = PAPF.PERSON_ID AND
PAA.DATE_START < P_END_DATE AND PAA.DATE_END = enDate AND
TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
PAPF.EFFECTIVE_END_DATE AND papf.business_group_id = 81;
END;
IF PREVIOUS_DATE_END IS NOT NULL THEN
IF ((P_END_DATE - HOLIDAYs) <> (PREVIOUS_DATE_END + 1)) THEN
holidays := 0;
/*else holidays := 2;*/
END IF;
END IF;
-------------------------------------------------------------------------------------------------
BEGIN
SELECT COUNT(*)
INTO L_COUNT_PRV
FROM PER_ABSENCE_ATTENDANCES PAA,
per_all_people_f PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAA.PERSON_ID = PAPF.PERSON_ID AND
paa.ABSENCE_ATTENDANCE_TYPE_ID IN (61) AND
TRUNC(PAA.DATE_END) = TRUNC(PREVIOUS_DATE_END) AND
--AND TRUNC(PAA.DATE_END) = TRUNC(TO_DATE('09-JAN-2009','DD-MON-YYYY'))
PAAF.ASSIGNMENT_NUMBER = P_EMPLOYEE_NUMBER AND
PAPF.PERSON_ID = PAAF.PERSON_ID AND
NVL(TRUNC(PPOS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE)) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE AND
PPOS.PERSON_ID = PAPF.PERSON_ID AND
TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND
TRUNC(PAPF.EFFECTIVE_END_DATE) AND
papf.business_group_id = 81;
END;
---------------------------------------------------------------------------------------------
IF (L_COUNT_PRV = 0) THEN
holidays := 0;
END IF;
RETURN(holidays);
END;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\
FUNCTION PFC_IN_CHECK_ABSENCE_OVERLAP(P_EMPLOYEE_NUMBER VARCHAR2,
P_START_DATE DATE,
P_END_DATE DATE) RETURN NUMBER IS
CURSOR C_COUNT IS
SELECT 1
FROM PER_ABSENCE_ATTENDANCES ABSENCE,
PER_ABSENCE_ATTENDANCE_TYPES ABSENCE_TYPES,
PER_ALL_PEOPLE_F PERSON
WHERE PERSON.PERSON_ID = ABSENCE.PERSON_ID
AND ABSENCE.ABSENCE_ATTENDANCE_TYPE_ID = ABSENCE_TYPES.ABSENCE_ATTENDANCE_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN PERSON.EFFECTIVE_START_DATE AND PERSON.EFFECTIVE_END_DATE
AND PERSON.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER
AND ((P_START_DATE BETWEEN ABSENCE.DATE_START AND ABSENCE.DATE_END) OR (P_END_DATE BETWEEN ABSENCE.DATE_START AND ABSENCE.DATE_END));
L_COUNT NUMBER := 0;
L_DUMMY C_COUNT%ROWTYPE;
BEGIN
OPEN C_COUNT;
FETCH C_COUNT
INTO L_DUMMY;
IF C_COUNT%FOUND THEN
L_COUNT :=L_COUNT + 1;
ELSE
L_COUNT :=0;
END IF;
CLOSE C_COUNT;
RETURN L_COUNT;
END;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Added on 25-Mar-11 to ensure employee do not enter Unpaid leave with start date and end date across two months-----
FUNCTION PFC_IN_LOP_CHECK(P_EMPLOYEE_NUMBER VARCHAR2,
P_START_DATE DATE,
P_END_DATE DATE) return number is
L_COUNT NUMBER := 0;
begin
IF (SUBSTR(TO_CHAR(P_START_DATE),4,3)=SUBSTR(TO_CHAR(P_END_DATE),4,3))
then
l_count:=0;
else
l_count:=1;
end if;
RETURN L_COUNT;
end;
------------------------------------------------------------------------------------------------------------------------------------
-----Added on 15-apr-11 to ensure employee do not enter the unpaid leave with start date and end date acroos weekends-----------
FUNCTION PFC_UPL_ACROSS_WEEKEND (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER
IS
l_count NUMBER;
p_date_end_day VARCHAR2 (150);
p_date_start_day VARCHAR2 (150);
BEGIN
BEGIN
SELECT decode(TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),'MONDAY','MONDAY',
TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),'MONDAY','MONDAY') into p_date_end_day
FROM hr_api_transactions ht, hr_api_transaction_steps hats, per_all_people_f papf
WHERE
(to_char(P_START_DATE+3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2
OR to_char(P_END_DATE+3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2)
and
PAPF.PERSON_ID=ht.CREATOR_PERSON_ID
and ht.transaction_id = hats.transaction_id
AND hats.information6 = 'Unpaid Leave'
AND PAPF.PERSON_ID=hats.CREATOR_PERSON_ID
AND ht.status = 'Y'
AND( REPLACE (TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'MONDAY'
OR
REPLACE (TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'MONDAY')
AND PAPF.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER;
/* SELECT TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY') into p_date_end_day
FROM hr_api_transactions ht, hr_api_transaction_steps hats, per_all_people_f papf
WHERE
hats.information2 = (TO_CHAR (p_end_date + 3, 'YYYY-MM-DD'))
and PAPF.PERSON_ID=ht.CREATOR_PERSON_ID
and ht.transaction_id = hats.transaction_id
AND hats.information6 = 'Unpaid Leave'
AND PAPF.PERSON_ID=hats.CREATOR_PERSON_ID
AND ht.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'MONDAY'
AND PAPF.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER;*/
/*SELECT TO_CHAR (TO_DATE (hats1.information2, 'YYYY/MM/DD'), 'DAY')
INTO p_date_start_day
FROM hr_api_transaction_steps hats1,
hr_api_transactions hat, --hr_lookups hrl1,
per_all_people_f papf1
WHERE
(to_char(P_START_DATE+3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2
OR to_char(P_END_DATE+3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2)
AND papf1.employee_number = p_employee_number
AND
hats1.creator_person_id = papf1.person_id
AND hats1.transaction_id = hat.transaction_id
AND hats1.information6 = 'Unpaid Leave'
AND hat.creator_person_id = papf1.person_id
AND hat.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats1.information1,
'YYYY/MM/DD'
),
'DAY'
),
' ',
''
) = 'MONDAY';*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found In p_end_date');
DBMS_OUTPUT.put_line ('p_date_end_day:' || p_date_end_day);
DBMS_OUTPUT.put_line ('p_emp' || p_employee_number);
DBMS_OUTPUT.put_line ('p_end_date' || p_end_date);
END;
BEGIN
SELECT decode(TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),'FRIDAY','FRIDAY',
TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),'FRIDAY','FRIDAY') INTO p_date_start_day
FROM hr_api_transactions ht, hr_api_transaction_steps hats, per_all_people_f papf
WHERE
(to_char(P_START_DATE-3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2
OR to_char(P_END_DATE-3,'YYYY-MM-DD') BETWEEN hats.information1 AND hats.information2)
and
PAPF.PERSON_ID=ht.CREATOR_PERSON_ID
and ht.transaction_id = hats.transaction_id
AND hats.information6 = 'Unpaid Leave'
AND PAPF.PERSON_ID=hats.CREATOR_PERSON_ID
AND ht.status = 'Y'
AND( REPLACE (TO_CHAR (TO_DATE (hats.information2, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'FRIDAY'
OR
REPLACE (TO_CHAR (TO_DATE (hats.information1, 'YYYY/MM/DD'), 'DAY'),
' ',
''
) = 'FRIDAY')
AND PAPF.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER;
/*SELECT TO_CHAR (TO_DATE (hats1.information1, 'YYYY/MM/DD'), 'DAY')
INTO p_date_start_day
FROM hr_api_transaction_steps hats1,
hr_api_transactions hat, --hr_lookups hrl1,
per_all_people_f papf1
WHERE
hats1.information1 =(TO_CHAR (p_start_date - 3, 'YYYY-MM-DD'))
AND
hats1.creator_person_id = papf1.person_id
AND hats1.transaction_id = hat.transaction_id
AND hats1.information6 = 'Unpaid Leave'
AND hat.creator_person_id = papf1.person_id
AND hat.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats1.information1,
'YYYY/MM/DD'
),
'DAY'
),
' ',
''
) = 'FRIDAY'
AND papf1.employee_number = p_employee_number;*/
/*SELECT TO_CHAR (TO_DATE (hats1.information1, 'YYYY/MM/DD'), 'DAY')
INTO p_date_start_day
FROM hr_api_transaction_steps hats1,
hr_api_transactions hat, --hr_lookups hrl1,
per_all_people_f papf1
WHERE
(to_char(P_START_DATE-3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2
OR to_char(P_END_DATE-3,'YYYY-MM-DD') BETWEEN hats1.information1 AND hats1.information2)
AND papf1.employee_number = p_employee_number
AND
hats1.creator_person_id = papf1.person_id
AND hats1.transaction_id = hat.transaction_id
AND hats1.information6 = 'Unpaid Leave'
AND hat.creator_person_id = papf1.person_id
AND hat.status = 'Y'
AND REPLACE (TO_CHAR (TO_DATE (hats1.information1,
'YYYY/MM/DD'
),
'DAY'
),
' ',
''
) = 'FRIDAY';*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found In p_start_date');
DBMS_OUTPUT.put_line ('p_date_start_day:' || p_date_start_day);
DBMS_OUTPUT.put_line ('p_emp' || p_employee_number);
DBMS_OUTPUT.put_line ('p_start_date' || p_start_date);
END;
IF p_date_end_day = 'MONDAY' OR p_date_start_day = 'FRIDAY'
THEN
l_count := 1;
ELSE
l_count := 0;
END IF;
RETURN (l_count);
END;
-----------------------------------------------------------------------------------------------------------------------------------------------------
-----Added on 15-apr-11 to ensure employee do not enter duplicate leave of leaves Pending for approval or Return for correction--
FUNCTION pfc_check_pending_leave (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER
IS
l_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM PER_ALL_PEOPLE_F PERSON,
APPS.HR_API_TRANSACTION_STEPS HATS2, --LEAVE PENDING FOR APPROVAL,
APPS.HR_API_TRANSACTIONS HAT2 --LEAVE PENDING FOR APPROVAL,
WHERE PERSON.EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER
AND HAT2.SELECTED_PERSON_ID=PERSON.PERSON_ID
AND HAT2.TRANSACTION_ID = HATS2.TRANSACTION_ID
AND TRUNC (SYSDATE) BETWEEN PERSON.EFFECTIVE_START_DATE AND PERSON.EFFECTIVE_END_DATE
AND HAT2.TRANSACTION_REF_TABLE = 'PER_ABSENCE_ATTENDANCES'
AND HAT2.STATUS IN ('Y', 'RI') --- Y is for Pending for approval status and RI is for Return for correction
AND (TO_DATE(HATS2.INFORMATION1,'YYYY-MM-DD') BETWEEN P_START_DATE AND P_END_DATE
OR TO_DATE(HATS2.INFORMATION2,'YYYY-MM-DD') BETWEEN P_START_DATE AND P_END_DATE);
/*FROM
per_all_people_f person,
apps.hr_api_transaction_steps hats2, --leave pending for approval,
apps.hr_api_transactions hat2 --leave pending for approval,
WHERE person.employee_number = p_employee_number
AND hat2.selected_person_id=person.person_id
AND hat2.transaction_id = hats2.transaction_id
AND TRUNC (SYSDATE) BETWEEN person.effective_start_date AND person.effective_end_date
AND hat2.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
AND hat2.status in ('Y', 'RI') --- Y is for Pending for approval status and RI is for Return for correction
and (TO_CHAR(TO_DATE(p_start_date),'YYYY-MM-DD') BETWEEN (hatS2.information1) AND (hatS2.information2)
or TO_CHAR(TO_DATE(p_END_date),'YYYY-MM-DD') BETWEEN (hatS2.information1) AND (hatS2.information2) ) ;*/
RETURN l_count;
END;
------------------------------------------------------------------------------------------------------------------------------------------------------------
-----Added on 15-apr-11 to ensure employee do not enter duplicate leave of existing approved leave--
FUNCTION pfc_in_check_approved_overlap (
p_employee_number VARCHAR2,
p_start_date DATE,
p_end_date DATE
--P_TRANSACTION_REF_ID NUMBER
)
RETURN NUMBER
IS
l_count NUMBER;
l_abs_id NUMBER;
l_trans_ref NUMBER;
l_start_date DATE;
l_end_date DATE;
BEGIN
BEGIN
SELECT absence_attendance_id, date_start, date_end
INTO l_abs_id, l_start_date, l_end_date
FROM per_absence_attendances paa
,per_all_people_f papf
WHERE paa.person_id = papf.person_id
AND papf.employee_number = p_employee_number
AND (date_start between p_start_date and p_end_Date OR date_end between p_start_date and p_end_date);
EXCEPTION WHEN OTHERS THEN
l_abs_id := 0;
END;
BEGIN
select hat.transaction_ref_id
INTO l_trans_ref
from hr_api_transactions hat
,hr_api_transaction_steps hats
where hat.transaction_id = hats.transaction_id
and FND_DATE.CANONICAL_TO_DATE(hats.information1) = l_start_date
and FND_DATE.CANONICAL_TO_DATE(hats.information2) = l_end_date
and hat.status = 'W';
EXCEPTION WHEN OTHERS THEN
l_trans_ref := 0;
END;
IF l_abs_id = l_trans_ref
THEN l_count := 0;
ELSE l_count := 1;
END IF;
RETURN l_count;
END;
-------------------------------------------------------------------------------------------------------------------------------------------
END PFC_IN_LEAVE_UTILITY_PKG;
/
After that we have to initialize the functions in BG Absence duration Fast Formula.
Navigation:
HR Super User àTotal Compensation à Basic à Write Formulas.
Query : BG_ABSENCE_DURATION in write formulas.
Then click edit button.
Here we need to initialize and add our functions.
----------------------------------------BG ABSENCE DURATION--------------------------------------
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
DEFAULT FOR ACP_SERVICE_START_DATE IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR PER_IN_PEOPLE_SEX IS 'X'
DEFAULT FOR ASG_NUMBER IS 'XXX'
DEFAULT FOR PER_SEX IS 'X'
DEFAULT FOR SYSDATE IS '2000/03/31 00:00:00' (date)
DEFAULT FOR TO_DATE IS '2000/03/31 00:00:00' (date)
DEFAULT FOR ASG_PAYROLL is 'X'
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text),
ABSENCE_ATTENDANCE_TYPE_ID (NUMBER),
/* ABS_ATTENDANCE_REASON_ID (NUMBER),*/
EFFECTIVE_DATE (DATE),
NET_ACCRUAL,
DATE (DATE),
NO_OF_DAYS (NUMBER),
PERSON_ID,
P_DATE (DATE)
error_or_warning = ' '
invalid_msg = ' '
v_dummy (text) = ' '
duration = '0'
number_of_days = 0
first_day_hours = 0
last_day_hours = 0
begin_day = '09:00'
end_day = '17:00'
SEX (TEXT)=' '
period_duration = 0
NET_ENTITLEMENT=0
TO_DAY =GET_DATE('SYSDATE')
OVERLAPPING (TEXT) = ' '
WEEK1 (TEXT) = ' '
WEEK2 (TEXT) = ' '
WEEK3 (TEXT) = ' '
WEEK4 (TEXT) = ' '
WEEK5 (TEXT) = ' '
WEEK6 (TEXT) = ' '
WEEK7 (TEXT) = ' '
WEEK8 (TEXT) = ' '
WEEK9 (TEXT) = ' '
WEEK10 (TEXT) = ' '
WEEK11 (TEXT) = ' '
WEEK12 (TEXT) = ' '
HOLIDAY (TEXT) = ' '
IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(
number_of_days = days_between(date_end,date_start)
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
ELSE
(
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60
last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60
if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60
last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(DAYS_BETWEEN(date_end,date_start) - 1)* hours_per_day)
)
)
ELSE
(
IF ((ABSENCE_ATTENDANCE_TYPE_ID = 2061) OR (ABSENCE_ATTENDANCE_TYPE_ID = 2063)) THEN
(
duration1 = to_char(PFC_IN_CHECK_CONSEC_HOLIDAYS(ASG_NUMBER,date_start,date_end))
)
ELSE
duration1 = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
IF ((ABSENCE_ATTENDANCE_TYPE_ID = 2061) OR (ABSENCE_ATTENDANCE_TYPE_ID = 2063)) THEN
(
duration2 = to_char(PFC_IN_CHECK_EFFECTIVE_HOLIDAY(ASG_NUMBER,date_start))
)
ELSE duration2 = to_char(0)
IF ((ABSENCE_ATTENDANCE_TYPE_ID = 2061) OR (ABSENCE_ATTENDANCE_TYPE_ID = 2063) OR (ABSENCE_ATTENDANCE_TYPE_ID = 61)) THEN
(
duration3 = to_char(PFC_IN_CHECK_EFFECTIVE_HDAYS(ASG_NUMBER, DATE_END))
)
ELSE duration3 = to_char(0)
IF ((ABSENCE_ATTENDANCE_TYPE_ID = 2061) OR (ABSENCE_ATTENDANCE_TYPE_ID = 2063) OR (ABSENCE_ATTENDANCE_TYPE_ID = 61)) THEN
(
duration4 = to_char(PFC_IN_CHECK_EFFECTIVE_HBDAYS(ASG_NUMBER, DATE_START))
)
ELSE duration4 = to_char(0)
IF (ABSENCE_ATTENDANCE_TYPE_ID = 2063) THEN
(
duration1 = to_char(PFC_IN_CHECK_CONSEC_HOLIDAYS(ASG_NUMBER,date_start,date_end)/2)
duration2 = to_char(to_num(duration2))
duration3 = to_char(to_num(duration3))
duration4 = to_char(to_num(duration4))
)
IF (ABSENCE_ATTENDANCE_TYPE_ID = 2066) THEN
(
duration1 = to_char(PFC_IN_CHECK_CONSEC_HOLIDAYS(ASG_NUMBER,date_start,date_end)/2)
duration2 = to_char(to_num(duration2))
duration3 = to_char(to_num(duration3))
duration4 = to_char(to_num(duration4))
)
duration = to_char(to_num(duration1)+to_num(duration2)+to_num(duration3)+to_num(duration4))
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
/*-------CHECK FOR GENDER FOR - MATERNITY - only Female are eligible for this leave-------*/
IF (ABSENCE_ATTENDANCE_TYPE_ID = 2064) THEN
(
IF (PER_SEX WAS NOT DEFAULTED) THEN
(
IF PER_SEX='M'
THEN
( duration='FAILED'
invalid_msg='Only females are allowed'
)
)
)
/*--------MATERNITY - No of days <= 84-------*/
IF (ABSENCE_ATTENDANCE_TYPE_ID = 2064) THEN
(
IF(PER_SEX WAS NOT DEFAULTED) THEN
(
IF PER_SEX ='F'
AND TO_NUM(duration) > 84 THEN
( duration='FAILED'
invalid_msg='Allowed only for 84 days'
)
)
)
/*--------Half Day leave to be applied only for one day -------*/
IF (ABSENCE_ATTENDANCE_TYPE_ID = 2063) OR (ABSENCE_ATTENDANCE_TYPE_ID = 2066) THEN
(
IF(date_start<>date_end) THEN
(
duration='FAILED'
invalid_msg='Half day leave can be applied only for a day'
)
)
/*--------Half Day Unpaid leave to be applied only for Mumbai Employees -------*/
IF (ABSENCE_ATTENDANCE_TYPE_ID = 2066) THEN
(
IF(ASG_PAYROLL <> 'PEIPL Monthly') THEN
(
duration='FAILED'
invalid_msg='You are not eligible for this leave'
)
)
/* ---------------absence overlap condition -------------
IF(PFC_IN_CHECK_ABSENCE_OVERLAP(ASG_NUMBER,date_start,date_end)<>0) THEN
(
duration='FAILED'
invalid_msg='Your absence days are overlapping with an existing absence'
)*/
/* ---------------LOP CHECK --------------*/
IF (ABSENCE_ATTENDANCE_TYPE_ID = 61) THEN
(
IF(PFC_IN_LOP_CHECK(ASG_NUMBER,date_start,date_end)<>0) THEN
(
duration='FAILED'
invalid_msg='Your absence days are crossing two months please submit two separate absence requests.'
)
)
/* ---------------Unpaid leave Check --------------*/
IF (ABSENCE_ATTENDANCE_TYPE_ID = 61) THEN
(
IF(PFC_UPL_ACROSS_WEEKEND (ASG_NUMBER,date_start,date_end)<>0) THEN
(
duration='FAILED'
invalid_msg='You have another unpaid leave pending for approval across the weekend.'
)
)
/* ---------------Pending leave overlap Check --------------
IF(pfc_check_pending_leave (ASG_NUMBER,date_start,date_end)<>0) THEN
(
duration='FAILED'
invalid_msg='You have another leave pending for approval/returned for correction for this period.'
)*/
/* ---------------Approved leave overlap Check --------------*/
IF(pfc_in_check_approved_overlap (ASG_NUMBER,date_start,date_end)<>0) THEN
(
duration='FAILED'
invalid_msg='You have another leave approved for this period.'
)
Return duration, invalid_msg
-------------------------------------------------------------------------------------------------------------------------------
Then click verify and save it.
Here we get the error for these validations.
Navigation:
1. Unpaid Leave across the two months.
I create a leave for 05-oct-2011 to 06-nov-2011.
2. Unpaid Leave across the Week ends.
First I create a absence unpaid leave for 12-oct-2011 to 14-oct-2011.
After submit the leave in pending approval. 14-oct-2011 is Friday.
Then I am creating another unpaid leave for 17-oct-2011 to 19-oct-2011. 17-oct-2011 is Monday.
5. Duplicate leaves for approved leave status: I already having 01-apr-2011 to 01-apr-2011 leave which status is approved. Then I
Am creating new leave for same date.
Here I displayed all validations output except duplicates for pending approval status.
Thank You.
