How to Delegation required for absence and business trip
Delegation should be required for absence and business trip request
1. Add Error Message to Message “Application Developer >> Application >> Message” and add 2 records like the table below:
Name
XX_DELEGATION_EXIST
XX_DELEGATION_EXIST
Lang
AR
US
App
Human Resources
Human Resources
Current Message text
البد من عمل تفويض قبل القيام بأجازة أو مهمة عمل
Delegation (Vacation Rule) should be created
first.
2. Submit request “Generate Messages” from application developer Responsibility for both languages (AR, US).
3. Add Function
a. To Specs
FUNCTION XX_DELEGATION_EXIST(V_person_ID IN NUMBER,V_START_DATE IN DATE,V_END_DATE IN
DATE)RETURN VARCHAR2;
b. To the body:
FUNCTION XX_DELEGATION_EXIST (
v_person_id
IN
NUMBER,
v_start_date
IN
DATE,
v_end_date
IN
DATE
)
RETURN VARCHAR2
AS
v_user_name
VARCHAR2 (10);
v_user_name_exist
NUMBER (10);
v_count
NUMBER;
v_supervisor_count
NUMBER;
BEGIN
-- Check if the employee has Username to the system.
SELECT COUNT (user_name)
INTO v_user_name_exist
FROM applsys.fnd_user usr, hr.per_all_people_f ppl
WHERE usr.employee_id = v_person_id
AND (TRUNC (usr.end_date) >= TRUNC (SYSDATE) OR usr.end_date IS NULL)
AND usr.employee_id = ppl.person_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (ppl.effective_start_date)
AND TRUNC (ppl.effective_end_date)
AND SUBSTR (usr.user_name, LENGTH (usr.user_name) - 2, 3) =
SUBSTR (ppl.employee_number, LENGTH (ppl.employee_number) - 2,
3);
-- Get User_Name
IF (v_user_name_exist = 0) THEN
RETURN 'Y';
ELSE
SELECT user_name
INTO v_user_name
FROM applsys.fnd_user usr, hr.per_all_people_f ppl
WHERE usr.employee_id = v_person_id
AND (TRUNC (usr.end_date) >= TRUNC (SYSDATE) OR usr.end_date IS NULL)
AND usr.employee_id = ppl.person_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (ppl.effective_start_date) AND TRUNC
(ppl.effective_end_date);
-- ND usr.user_name = ppl.employee_number; -- in case the Developer assigned his user
to existing exployee.
END IF;
-- Check if the employee has subordinates or not
SELECT NVL (COUNT (*), 0)
INTO v_supervisor_count
FROM hr.per_all_assignments_f
WHERE supervisor_id = v_person_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (effective_start_date)
AND TRUNC (effective_end_date);
-- check if delegation exist for employee or not
IF v_supervisor_count > -1
THEN
SELECT NVL (COUNT (*), 0)
INTO v_count
FROM applsys.wf_routing_rules wfr
WHERE
--V_START_DATE between wfr.BEGIN_DATE and nvl(wfr.end_date,to_date('01-012417','dd-mm-yyyy'))
(
TO_DATE (v_start_date, 'dd-mm-yyyy') =
TO_DATE (wfr.begin_date, 'dd-mm-yyyy')
OR v_start_date BETWEEN wfr.begin_date
AND NVL (wfr.end_date,
TO_DATE ('01-01-2417',
'dd-mm-yyyy')
)
)
AND wfr.ROLE = v_user_name
AND v_end_date <= wfr.end_date;
-- check if delegation exist
IF v_count >0 THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;
ELSE
RETURN 'Y';
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'N';
END;
4. Add Validation in (EIT UserHook/ SIT Validation / Absence Userhook,...)
-- for absence you will add start, end date instead of the segments
SELECT NVL(XX_DELEGATION_EXIST( SUBSTR(:NEW.segment30,15) ,fnd_date.canonical_to_date
(:NEW.segment7),fnd_date.canonical_to_date (:NEW.segment8)),0)
INTO l_Delegation_Exist
FROM dual;
IF
l_Delegation_Exist = 'N'
THEN
hr_utility.set_message (800, 'XX_DELEGATION_EXIST');
hr_utility.raise_error;
END IF;
5. Test: should give message like this is vacation rule does not exist for the same period (from professional screens or self services)
© Copyright 2024