ASG_SUP_FULL_NAME will give you the name of the supervisor for given assignment - Example for your record it will give the name of your manager.
Your requirement is to check if the assignment which is used whether he is a manager or not, for which you will have to check if that person is assigned as supervisor for any other assignments.
Hope it clarifies.
If for all the managers, you use the check_box on the assignment screen, then you can use the DBI ASG_MANAGER
But if for some managers, users have not ticked the checkbox you would not get the correct result.
select * from per_all_assignments_f
where manager_flag = 'Y'
and sysdate between effective_start_date and effective_end_date ;
A Formula function is the best way of doing it. Use this code -
FUNCTION isManager (p_person_id NUMBER)
l_Manager_flag VARCHAR2(1) ;
l_Manager_flag := 'N';
FOR csr in ( select * from per_all_assignments_f
where supervisor_id = p_person_id
and trunc(sysdate) between effective_start_date and effective_end_date) LOOP
l_Manager_flag := 'Y';
It depends on where you're using the formula function.
If it is payroll related assignment_id should be used as a payroll is run for assignment, and based on the flow the person_id context might not be set.
For others which are specific to person(say Absence is always done at person level) you can use person_id.
So it depends on which level you're using the function.