Oracle HCM: Restricting Absence Type List of Values in Oracle Self-Service Human Resources

Use the following the sample steps to derive a restricted list of values for absence type LOV in Absence Management. The custom logic has to be written in the New Package function
HR_ABSENCE_RESTRICTED.ABSENCES_RESTRICTED.

Login person id and selected person id will be passed by default – using these two parameters the rest of the data or parameters can be queried from the tables

The above procedure will be called from the AbsenceTypeVOImpl java file with the proper input parameters and the return value will be appended to the AbsenceTypeVO

We return the absence type ids of the absence we dont want the user to see.

Sample function:

FUNCTION absences_restricted (selected_person_id IN VARCHAR2,
login_person_id IN VARCHAR2)
RETURN VARCHAR2
IS
l_abs_type_id VARCHAR2 (100);
l_abs_type_value NUMBER;
BEGIN
SELECT COALESCE (
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Male'
AND NVL (
hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
),
'Single'
) IN
('Single', 'Widowed')
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) IS NULL
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Paternity Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Female'
AND NVL (
hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
),
'Single'
) IN
('Single', 'Widowed')
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) IS NULL
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Paternity Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Male'
AND hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
) = 'Married'
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) IS NULL
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Marriage Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Female'
AND hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
) = 'Married'
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) IS NULL
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Marriage Leave',
'Paternity Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Male'
AND NVL (
hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
),
'Single'
) IN
('Single', 'Widowed')
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) = 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Paternity Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Female'
AND NVL (
hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
),
'Single'
) IN
('Single', 'Widowed')
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) = 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Paternity Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Male'
AND hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
) = 'Married'
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) = 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Marriage Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Female'
AND hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
) = 'Married'
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) = 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Marriage Leave',
'Paternity Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Male'
AND hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
) = 'Married'
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) != 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Marriage Leave',
--'paternity leave',
'Hajj Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Female'
AND NVL (
hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
),
'Single'
) IN
('Single', 'Widowed')
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) != 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Paternity Leave',
'Hajj Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Female'
AND hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
) = 'Married'
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) != 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Marriage Leave',
'Paternity Leave',
'Hajj Leave','Unpaid Leave'))
END),
TO_CHAR(CASE
WHEN hr_general.decode_lookup ('SEX', papf.sex) =
'Male'
AND NVL (
hr_general.decode_lookup (
'MAR_STATUS',
papf.marital_status
),
'Single'
) IN
('Single', 'Widowed')
AND hr_general.decode_lookup (
'AE_RELIGION',
papf.per_information10
) != 'Muslim'
THEN
(SELECT listagg (
absence_attendance_type_id,
','
)
WITHIN GROUP (ORDER BY
absence_attendance_type_id)
FROM per_absence_attendance_types
WHERE name IN
('Maternity Leave',
'Paternity Leave',
'Hajj Leave','Unpaid Leave'))
END)
)
abs_type_id
into l_abs_type_id
FROM per_all_people_f papf
WHERE 1 = 1 AND current_employee_flag = 'Y'
AND person_id =selected_person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
select XX_Mgr_Shw_Unpd_leave (login_person_id ) into l_abs_type_value from dual;
l_abs_type_id := l_abs_type_id ||','|| l_abs_type_value ;
dbms_output.put_line (' l_abs_type_id2 ' || l_abs_type_id);

RETURN l_abs_type_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;

END absences_restricted;

VO query would be updated as per the below sample.

SELECT * FROM (SELECT paat.ABSENCE_ATTENDANCE_TYPE_ID , paat.BUSINESS_GROUP_ID , paat.INPUT_VALUE_ID , paat.DATE_EFFECTIVE , paattl.NAME , paat.ABSENCE_CATEGORY , paat.DATE_END , paat.HOURS_OR_DAYS , paat.INCREASING_OR_DECREASING_FLAG , paat.REQUEST_ID , paat.PROGRAM_APPLICATION_ID , paat.PROGRAM_ID , paat.PROGRAM_UPDATE_DATE , paat.ATTRIBUTE_CATEGORY , paat.ATTRIBUTE1 , paat.ATTRIBUTE2 , paat.ATTRIBUTE3 , paat.ATTRIBUTE4 , paat.ATTRIBUTE5 , paat.ATTRIBUTE6 , paat.ATTRIBUTE7 , paat.ATTRIBUTE8 , paat.ATTRIBUTE9 , paat.ATTRIBUTE10 , paat.ATTRIBUTE11 , paat.ATTRIBUTE12 , paat.ATTRIBUTE13 , paat.ATTRIBUTE14 , paat.ATTRIBUTE15 , paat.ATTRIBUTE16 , paat.ATTRIBUTE17 , paat.ATTRIBUTE18 , paat.ATTRIBUTE19 , paat.ATTRIBUTE20 , paat.LAST_UPDATE_DATE , paat.LAST_UPDATED_BY , paat.LAST_UPDATE_LOGIN , paat.CREATED_BY , paat.CREATION_DATE , paat.COMMENTS from per_absence_attendance_types paat, per_abs_attendance_types_tl paattl where paat.BUSINESS_GROUP_ID= :1 and (paat.date_end is null OR paat.date_end > sysdate) and (paat.date_effective < sysdate) and paat.absence_attendance_type_id = paattl.absence_attendance_type_id and paattl.language = userenv('LANG') and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB' and paat.absence_category not in ('M','GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO','GB_ADDL_PAT_ADOPT','GB_ADDL_PAT_BIRTH','GB_SPL_BIRTH','GB_SPL_ADOPT')) or (hr_api.return_legislation_code(paat.business_group_id) <> 'GB' and paat.absence_category not in ('GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO','GB_ADDL_PAT_ADOPT','GB_ADDL_PAT_BIRTH' ,'GB_SPL_BIRTH','GB_SPL_ADOPT')))) QRSLT WHERE (ABSENCE_ATTENDANCE_TYPE_ID not in(63,65,67,1063,67)) ORDER BY Name

Oracle HCM

Monday, 13 November 2017

Restricting Absence Type List of Values in Oracle Self-Service Human Resources

No comments:

Post a Comment

Pageviews last month