Custom Approver Group for Approval by the first supervisor, at most.SSHR Oracle Apps R12

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;