Hello
We currently developing the solution for the integrations from IFS, part of the data we want to extract is Employee’s Manager/Supervisor, but due to technical limitations we only able to use table and not views or API calls. Is there a TABLE in IFS database which contains employee’s manager/supervisor or at least a path via few tables which could provide this information? Thanks
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_noThe 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;
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.