Requirement:
Approval by the first supervisor, at most. If the employee
applies, it works fine.
But if the supervisor applies for employee, and supervisor
has supervisor attached is assignment screen, then it goes to his supervisor,
which is not correct in this case.
Then, we need to create custom approver group.
SELECT TYR_GET_SUPERVISOR(:transactionId) FROM DUAL
FUNCTION TYR_GET_SUPERVISOR(p_transaction_id IN NUMBER
)
---Returns the person_id of first supervisor, if supervisor applies for the employeee no need to bring the supervisor's supervisor------
RETURN VARCHAR2
AS
L_person_id NUMBER(10);
L_selected_person_id hr_api_transactions.selected_person_id%TYPE;
L_creator_person_id hr_api_transactions.creator_person_id%TYPE;
l_business_group_id NUMBER;
BEGIN
fnd_profile.get ('PER_BUSINESS_GROUP_ID', l_business_group_id);
BEGIN
SELECT selected_person_id,creator_person_id
INTO L_selected_person_id,L_creator_person_id
FROM hr_api_transactions
WHERE transaction_id = p_transaction_id;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
BEGIN
SELECT paaf.supervisor_id
INTO l_person_id
FROM per_all_people_f papf,
per_all_assignments_f paaf
WHERE papf.person_id=paaf.person_id
and papf.person_id=l_selected_person_id
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND PRIMARY_FLAG = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_person_id :=null;
END;
IF (L_selected_person_id = L_person_id) OR (l_creator_person_id = l_person_id) THEN
RETURN NULL;
ELSE
RETURN 'PER:'||to_char(L_person_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END TYR_GET_SUPERVISOR;
