"SELECT s.org_bu_id org_bu_id,
s.person_id supervisor_person_id,
s.company_id supervisor_company_id,
s.emp_no supervisor_emp_no,
Decode(s.emp_no, NULL, 'PERSON', 'EMPLOYEE') supervisor_bu_type_db,
Business_Unit_Type_API.Decode(Decode(s.emp_no, NULL, 'PERSON', 'EMPLOYEE')) supervisor_bu_type,
b.company_id emp_company_id,
b.emp_no emp_no,
b.business_unit_id emp_bu_id,
r.position_id emp_pos_code,
s.access_role_id access_role,
GREATEST(r.valid_from,s.valid_from) valid_from,
LEAST(r.valid_to, s.valid_to) valid_to,
DECODE((CASE WHEN trunc(sysdate) BETWEEN r.valid_from AND r.valid_to THEN orig_r.general_access
ELSE 'FALSE' END),'TRUE', s.trans_from, GREATEST(s.trans_from, r.valid_from)) trans_from,
DECODE((CASE WHEN trunc(sysdate) BETWEEN r.valid_from AND r.valid_to THEN orig_r.general_access
ELSE 'FALSE' END),'TRUE', s.trans_to, LEAST(s.trans_to, r.valid_to)) trans_to,
r.general_access general_access,
o.business_unit_type org_bu_type,
DECODE (o.business_unit_type, 'CUSTOM', o.business_unit_code, 'ORGANIZATION', o.org_code) org_code,
o.employee_group_id employee_group,
o.person_group_id person_group,
r.relation_id relation_id,
s.relation_id supervisor_relation_id,
s.pos_code supervisor_pos_code,
s.structure_id structure_id,
Business_Unit_API.Get_Structure_Type(s.structure_id) structure_type,
DECODE (orig_r.relation_type, 'ACCESS', orig_r.related_to, orig_r.business_unit_id) orig_bu_id,
orig_r.include_sub include_sub,
DECODE (orig_r.relation_type, 'ACCESS', 'FALSE', 'TRUE') organization_assignment
FROM business_unit_relation_tab r,
business_unit_tab b,
business_unit_tab o,
business_unit_relation_tab orig_r,
company_emp ce,
(SELECT a.emp_no,
a.company_id,
a.person_id,
a.org_bu_id,
NVL(p.access_role_id, a.access_role_id) access_role_id,
GREATEST(a.valid_from, NVL(p.valid_from,a.valid_from)) valid_from,
LEAST(a.valid_to, NVL(p.valid_to,a.valid_to)) valid_to,
GREATEST(a.trans_from, NVL(p.valid_from,a.trans_from)) trans_from,
LEAST(a.trans_to, NVL(p.valid_to,a.trans_to)) trans_to,
p.avail_pos_code emp_pos_code,
a.relation_id,
a.pos_code,
Business_Unit_API.Get_Struct_Id_By_Access(a.org_bu_id) structure_id
FROM bu_access_temp_tab a,
business_unit_tab s,
company_authorization_temp_tab p
WHERE s.business_unit_id = a.org_bu_id
AND BITAND(nvl(p.access_area, a.access_area), 2 /*User_Access_API.DB_EMPLOYEE_DATA_ACCESS*/) > 0
AND (a.relation_type = 'ACCESS' AND a.pos_code IS NULL OR p.struct_bu_id = s.struct_bu_id)
AND p.pos_code(+) = a.pos_code
AND p.valid_from(+) <= a.valid_to
AND p.valid_to(+) >= a.valid_from) s
WHERE r.business_unit_id = s.org_bu_id
AND r.relation_type = 'OPERATIONAL'
AND r.related_to = b.business_unit_id
AND b.business_unit_type = 'EMPLOYEE'
AND ce.company = b.company_id
AND ce.employee_id = b.emp_no
AND ce.person_id <> s.person_id
AND EXISTS (SELECT u.company
FROM user_finance_tab u, person_info_tab p
WHERE u.company = b.company_id
AND u.userid = p.user_id
AND p.person_id = s.person_id)
AND (s.emp_pos_code IS NULL OR s.emp_pos_code = NVL(r.position_id,' '))
AND r.valid_from <= s.valid_to
AND r.valid_to >= s.valid_from
AND o.business_unit_id = r.business_unit_id
AND orig_r.relation_id = s.relation_id
WITH READ ONLY
"