Fast Formula Navigation:
-------------------------
Total Compensation --> Basic --> Write Formul --> Fast Formula.
BG Absence Formula:
-----------------------
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'
)
/*---Unpaid Leave across 2 months 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 across two payroll month periods. Please submit two separate absence requests'
)
)
Return duration, invalid_msg
Fast Formula Package Spec and Body:
----------------------------------------
---------------------------------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;
/
