Employee's manager/supervisor table in the database

@ArturasVit if you would have had access to DB views, then this would have been easy.

Just querying the sup_emp_no from DB View company_person_all.

sup_emp_no is fetched with a getter: 

Company_Pers_Assign_API.Get_Direct_Sup_Person_List(company_id,  emp_no,sysdate, NULL, 'TRUE', NULL, NULL, 'TRUE') sup_emp_no

The tricky part is figuring the result of the getter. It is quite complex. Below you have the code for the PL/SQL procedure. Good luck. 

PROCEDURE Get_Direct_Supervisor_List (
   person_id_list_  OUT VARCHAR2,
   name_list_       OUT VARCHAR2,
   id_name_list_    OUT VARCHAR2,
   company_id_      IN VARCHAR2,
   emp_no_          IN VARCHAR2,
   valid_from_      IN DATE DEFAULT SYSDATE,
   return_pos_code_ IN VARCHAR2 DEFAULT NULL,
   formatted_       IN VARCHAR2 DEFAULT NULL,
   org_code_        IN VARCHAR2 DEFAULT NULL,
   pos_code_        IN VARCHAR2 DEFAULT NULL,
   return_name_     IN VARCHAR2 DEFAULT NULL,
   max_value_       IN VARCHAR2 DEFAULT NULL)
IS
   account_date_    DATE;
   --Bug 144954, start
   sup_name_        VARCHAR2(10000);
   sup_person_      VARCHAR2(10000) := '^';
   sup_person_id_name_ VARCHAR2(10000) := '^';
   sup_person_name_ VARCHAR2(10000) := '^';
   --Bug 144954, end
   found_           BOOLEAN := FALSE;
   struct_id_       business_unit_tab.business_unit_code%TYPE;
   struct_type_     business_unit_tab.business_unit_type%TYPE;
   emp_pos_code_    company_pers_assign_tab.pos_code%TYPE;
   sup_pos_code_    company_pers_assign_tab.pos_code%TYPE;
   emp_org_code_    company_pers_assign_tab.org_code%TYPE;
   bus_unit_assignment_ company_pers_assign_tab.bus_unit_assignment%TYPE;
   emp_org_bu_id_   business_unit_tab.business_unit_id%TYPE;
   emp_person_id_  VARCHAR2(30);
   struct_bu_id_   business_unit_tab.business_unit_id%TYPE;
   dir_sup_logic_   VARCHAR2(10);
   is_rel_valid_    BOOLEAN;
   CURSOR get_primary_asst IS
      SELECT pos_code, org_code, bus_unit_assignment
      FROM company_pers_assign_tab
      WHERE company_id = company_id_
      AND emp_no = emp_no_
      AND account_date_ BETWEEN valid_from AND valid_to
      AND primary = '1';

   CURSOR get_org_pos IS
      SELECT pos_code, org_code, bus_unit_assignment
      FROM company_pers_assign_tab
      WHERE company_id = company_id_
      AND emp_no = emp_no_
      AND org_code LIKE nvl(org_code_, '%')
      AND pos_code LIKE nvl(pos_code_, '%')
      AND account_date_ BETWEEN valid_from AND valid_to;        

   CURSOR get_struct_id IS
      SELECT b2.business_unit_code, org.struct_bu_id
      FROM company_org_tab org, business_unit_tab b2
      WHERE org.company_id = company_id_
      AND org.org_code = emp_org_code_
      AND b2.business_unit_id = org.struct_bu_id;

   CURSOR get_sup IS
      SELECT DISTINCT person_id, relation_id, relation_type
      FROM bu_access_temp_tab
      WHERE org_bu_id = emp_org_bu_id_
      AND pos_code = sup_pos_code_
      AND account_date_ BETWEEN valid_from AND valid_to
      AND account_date_ BETWEEN trans_from AND trans_to
      AND person_id <> emp_person_id_;

   CURSOR get_all_children_matrixes(org_bu_id_ IN VARCHAR2) IS
      SELECT DISTINCT org_bu_id business_unit_id, valid_from, valid_to
      FROM org_structure_matrix
      START WITH sup_org_bu_id = org_bu_id_
      AND trunc(account_date_) <= valid_to
      CONNECT BY NOCYCLE PRIOR org_bu_id = sup_org_bu_id AND PRIOR valid_from <= valid_to AND PRIOR valid_to >= valid_from;

   CURSOR get_all_children_companies(org_bu_id_ IN VARCHAR2) IS
      SELECT DISTINCT org_bu_id business_unit_id, valid_from, valid_to
      FROM org_structure_company
      START WITH ((sup_org_bu_id is null and struct_bu_id = org_bu_id_) OR (sup_org_bu_id = org_bu_id_))
      AND trunc(account_date_) <= valid_to
      CONNECT BY NOCYCLE PRIOR org_bu_id = sup_org_bu_id AND PRIOR valid_from <= valid_to AND PRIOR valid_to >= valid_from;
   -- New Direct Supervisor Logic
   CURSOR get_sup_new IS
      SELECT * 
      FROM TABLE(Company_Pers_Assign_API.Get_Direct_Supervisor_Table(company_id_, emp_no_, valid_from_, org_code_, pos_code_));
BEGIN
   General_SYS.Init_Method(Company_Pers_Assign_API.lu_name_, 'Company_Pers_Assign_API', 'Get_Direct_Supervisor_List');
   account_date_ := TRUNC(valid_from_);
   person_id_list_ := NULL;
   name_list_ := NULL;
   id_name_list_ := NULL;
   emp_person_id_ := Company_Person_API.Get_Person_Id(company_id_, emp_no_);

   IF (pos_code_ IS NULL) AND (org_code_ IS NULL) THEN
      OPEN get_primary_asst;
      FETCH get_primary_asst INTO emp_pos_code_, emp_org_code_, bus_unit_assignment_;
      CLOSE get_primary_asst;
   ELSE
      OPEN get_org_pos;
      FETCH get_org_pos INTO emp_pos_code_, emp_org_code_, bus_unit_assignment_;
      CLOSE get_org_pos;
   END IF;

   IF (emp_pos_code_ IS NOT NULL) AND (emp_org_code_ IS NOT NULL) THEN
      IF bus_unit_assignment_ = 'TRUE' THEN
         OPEN get_struct_id;
         FETCH get_struct_id INTO struct_id_, struct_bu_id_;
         CLOSE get_struct_id;      
         struct_type_ := 'CUSTOM';
         emp_org_bu_id_ := Business_Unit_API.Get_Bus_Unit_Id_By_Type(company_id_, 'CUSTOM', emp_org_code_, struct_bu_id_);
      ELSE
         struct_id_ := company_id_;
         struct_type_ := 'COMPANY';
         emp_org_bu_id_ := Business_Unit_API.Get_Bus_Unit_Id_By_Type(company_id_, 'ORGANIZATION', emp_org_code_, struct_bu_id_);
      END IF;

      IF struct_id_ IS NOT NULL THEN
         sup_pos_code_ := Position_Util_API.Get_Sup_Pos_Code(struct_id_, emp_pos_code_, account_date_, struct_type_);

         -- Bug 148777, Start
         IF sup_pos_code_ <> '*' THEN

            dir_sup_logic_ := Business_Unit_API.Get_Dir_Sup_Logic(Company_Org_API.Get_Struct_Bu_Id(company_id_, emp_org_code_));

            IF dir_sup_logic_ = 'LOGIC1' THEN

               FOR sup_ IN get_sup LOOP
                  IF (INSTR(sup_person_, (sup_.person_id || '^')) = 0) THEN
                     found_ := TRUE;
                     is_rel_valid_ := FALSE;
                     IF lengthb(sup_person_ || sup_.person_id || '^') > 4000 AND max_value_ = 'TRUE' THEN
                        EXIT;
                     ELSE
                        IF Business_Unit_Relation_API.Get_Business_Unit_Id(sup_.relation_id) != emp_org_bu_id_ AND sup_.relation_type = 'OPERATIONAL' THEN 
                           FOR rec_ IN get_all_children_matrixes(Business_Unit_Relation_API.Get_Business_Unit_Id(sup_.relation_id)) LOOP
                              IF rec_.business_unit_id = emp_org_bu_id_ THEN
                                 is_rel_valid_ := TRUE;
                                 EXIT;
                              END IF;
                           END LOOP;
                           IF is_rel_valid_ = FALSE THEN
                              FOR rec_ IN get_all_children_companies(Business_Unit_Relation_API.Get_Business_Unit_Id(sup_.relation_id)) LOOP
                                 IF rec_.business_unit_id = emp_org_bu_id_ THEN
                                    is_rel_valid_ := TRUE;
                                    EXIT;
                                 END IF; 
                              END LOOP;  
                           END IF;
                        ELSE
                           is_rel_valid_ := TRUE;
                        END IF;
                        IF is_rel_valid_ THEN
                           sup_person_ := sup_person_ || sup_.person_id || '^';
                        END IF;
                     END IF;

                     IF return_name_ = 'TRUE' THEN
                        IF is_rel_valid_ THEN 
                           sup_name_ := Pers_API.Get_Internal_Display_Name(sup_.person_id);
                        END IF;
                        -- Bug 147930, Start
                        IF lengthb(sup_person_id_name_ || sup_.person_id || ' - ' || sup_name_ || '^') > 4000 AND max_value_ = 'TRUE' THEN
                           EXIT;
                        ELSE
                           IF is_rel_valid_ THEN
                              sup_person_name_ := sup_person_name_ || sup_name_ || '^';
                              sup_person_id_name_ := sup_person_id_name_ || sup_.person_id || ' - ' || sup_name_ || '^';
                           END IF;
                        END IF;
                        -- Bug 147930, End
                     END IF;
                  END IF;
               END LOOP;
            ELSE
               -- New Direct Supervisor Logic            
               FOR rec_ IN get_sup_new LOOP
                  IF (INSTR(sup_person_, (rec_.person_id || '^')) = 0) THEN
                     found_ := TRUE;
                     IF lengthb(sup_person_ || rec_.person_id || '^') > 4000 AND max_value_ = 'TRUE' THEN
                        EXIT;
                     ELSE
                        sup_person_ := sup_person_ || rec_.person_id || '^';
                     END IF;

                     sup_pos_code_ := rec_.pos_code;

                     IF return_name_ = 'TRUE' THEN
                        sup_name_ := Pers_API.Get_Internal_Display_Name(rec_.person_id);
                        IF lengthb(sup_person_id_name_ || rec_.person_id || ' - ' || sup_name_ || '^') > 4000 AND max_value_ = 'TRUE' THEN
                           EXIT;
                        ELSE
                           sup_person_name_ := sup_person_name_ || sup_name_ || '^';
                           sup_person_id_name_ := sup_person_id_name_ || rec_.person_id || ' - ' || sup_name_ || '^';
                        END IF;
                     END IF;
                  END IF;
               END LOOP;
            END IF;
         END IF;
         -- Bug 148777, End
      END IF;
   END IF;

   IF found_ = TRUE THEN
     IF formatted_ = 'TRUE' THEN
        person_id_list_ := substr(sup_person_, 2, length(sup_person_) - 2);
     ELSE
        person_id_list_ := sup_person_;
     END IF;
     id_name_list_ := substr(sup_person_id_name_, 2, length(sup_person_id_name_) - 2);
     name_list_ := substr(sup_person_name_, 2, length(sup_person_name_) - 2);
  ELSIF return_pos_code_ = 'TRUE' THEN
     person_id_list_ := sup_pos_code_;
  END IF;
END Get_Direct_Supervisor_List;

In the end we have decided to use different approach and only look for employee’s superior position holder. While this does not make someone a manager in IFS (OU access additionally needs to be granted), it does actually fit the positional structure of our organization. 

If anyone comes accross this there are a few ways to play around this. There can be multiple direct supervisors based on the set up, orgs, positions, etc.

select *    from table(ifsapp.company_pers_assign_api.Get_Direct_Supervisor_Table(company_, emp_no_))

This will retrieve the list of “direct” supervisor(s) (meaning the first available person(s) above in the hiearchy based on structure setup)

(Here, DIRECT_SUB being set to FALSE is because this supervisor is not in the direct position above the employee I’m querying, but there is no one actually set in that above position, so IFS automatically goes to the next position/org up).

I’ve created a little quick and dirty recursive PLSQL Method to be able to feed two person IDs and get a result to know if someone is a supervisor of another, even indirectly (so in effect, feeding the CEO person ID into the supervisor argument would always return TRUE). This is basically a way to check two person’s reporting lines “intersect”. (I use this in some Mods to prevent some actions being taken on objects unless the user is part of the reporting line of some person recorded in the object, for example, preventing a user from modifying a Service Contract unless the user is the Service Contract Coordinator or one of the Coordinator’s supervisors, their supervisors’ supervisors’, etc. up the chain to CEO)

There might have been an easier way to achieve this or there might even be an out of the box function that does this, but I couldn’t find one =p

function is_supervisor(person_id_ in varchar2, supervisor_id_ in varchar2) RETURN VARCHAR2

IS

  person_id_rec_ ifsapp.person_info_api.public_rec;
  supervisor_rec_ ifsapp.person_info_api.public_rec;

  cursor get_emp_rec(person_id_ in varchar2) IS
  select company, employee_id
  from ifsapp.company_emp_tab t
  where t.person_id = person_id_;

  break_loop_ BOOLEAN := FALSE;

  function supervisor_check(company_id_ in varchar2, emp_no_ in varchar2, supervisor_id_ in varchar2) RETURN BOOLEAN
    IS

  dummy_ boolean;

  cursor sups(company_id_ in varchar2, emp_no_ in varchar2) IS
  select *
  from table(ifsapp.company_pers_assign_api.Get_Direct_Supervisor_Table(company_id_, emp_no_));

  BEGIN

       for rec_ in sups(company_id_, emp_no_) LOOP


             if rec_.person_id = supervisor_id_ then
               break_loop_ := TRUE;
             else
               dummy_ := supervisor_check(rec_.company_id, rec_.emp_no, supervisor_id_);
             end if;

            if break_loop_ then
              exit;
            end if;

       end loop;

  IF NOT break_loop_ then

     RETURN FALSE;

  else

     RETURN TRUE;

  end if;

  END supervisor_check;

begin

person_id_rec_ := ifsapp.person_info_api.get(person_id_);
supervisor_rec_ := ifsapp.person_info_api.get(supervisor_id_);

if person_id_rec_.person_id is null or supervisor_rec_.person_id is null then
  RETURN NULL;
end if;

  for rec_ in get_emp_rec(person_id_) LOOP

    if supervisor_check(rec_.company,rec_.employee_id, supervisor_id_) then
      RETURN 'TRUE';
    end if;

  end loop;

RETURN 'FALSE';

end is_supervisor;

@SimonTestard nice trick with the PL/SQL function. Thanks for sharing.