Summary
Span of Control is a count of how many employees report to a given manager. Direct and indirect report counts should be split into their own totals. Other counts are needed, including many vacancies for direct and indirect reports there are in the organisation. A manager is any position that has other positions reporting to it. The reporting path from the top to anywhere in the tree is required to flatten the structure.
I've seen this problem appear often in HR reporting and data warehouse projects. I have only been able to solve it in Oracle. Can this report be written in (ANSI) SQL that is compatible with another database, such as SQL Server or PostgreSQL?
Detail
Visual representation of an organisation hierarchy:
Level 1 1:3
|
----------------+-----------------------------------
| | | |
Level 2 2:1 13: 10:12 4:2
| |
---------+---------- ----------+----------
| | | | | |
Level 3 12:10 3: 3: 5:10-1 11:11 6:
| | |
---+--- ------------+------------ |
| | | | | | | |
Level 4 7:4 7:9 8:5 8:7 8:6 8: 8: 9:8
Each node or leaf of the tree is represented by one of the following:
position_id:employee_idposition_id:employee_id-multi_job_sequence (if multi_job_sequence>0)position_id: (vacant)Expected output
POSITION_ID POSITION_DESCR REPORTSTO_POSITION_ID EMPLOYEE_ID MULTI_JOB_SEQUENCE EMPLOYEE_NAME TREE_LEVEL_NUM IS_MANAGER MAX_INCUMBENTS FILLED_HEAD_COUNT VACANT_HEAD_COUNT FILLED_DIRECT_REPORTS VACANT_DIRECT_REPORTS FILLED_INDIRECT_REPORTS VACANT_INDIRECT_REPORTS EMPLOYEES_UNDER_POSITION VACANCIES_UNDER_POSITION REPORTING_PATH_POSITION_ID REPORTING_PATH_POSITION_DESCR REPORTING_PATH_EMPLOYEE REPORTING_PATH_EMPLOYEE_NAME
1 CEO NULL 3 0 Jill 1 1 1 1 0 3 1 9 5 12 6 1 CEO 3 Jill
2 Senior Manager 1 1 0 Tom 2 1 1 1 0 1 2 2 0 3 2 1>2 CEO>Senior Manager 3>1 Jill>Tom
3 West Winger 2 NULL NULL NULL 3 0 2 0 2 0 0 0 0 0 0 1>2>3 CEO>Senior Manager>West Winger 3>1>(vacant) Jill>Tom>(vacant)
4 Executive Assistant 1 2 0 Doug 2 0 1 1 0 0 0 0 0 0 0 1>4 CEO>Executive Assistant 3>2 Jill>Doug
5 Supervisor South 10 10 1 Frank 3 1 1 1 0 3 2 0 0 3 2 1>10>5 CEO>Senior Manager>Supervisor South 3>12>10-1 Jill>Fred>Frank
6 Supervisor East 10 NULL NULL NULL 3 1 1 0 1 1 0 0 0 1 0 1>10>6 CEO>Senior Manager>Supervisor East 3>12>(vacant) Jill>Fred>(vacant)
7 Expert 12 4 0 Olivia 4 0 2 2 0 0 0 0 0 0 0 1>2>12>7 CEO>Senior Manager>Supervisor West>Expert 3>1>10>4 Jill>Tom>Frank>Olivia
7 Expert 12 9 0 David 4 0 2 2 0 0 0 0 0 0 0 1>2>12>7 CEO>Senior Manager>Supervisor West>Expert 3>1>10>9 Jill>Tom>Frank>David
8 Minion 5 5 0 Carol 4 0 5 3 2 0 0 0 0 0 0 1>10>5>8 CEO>Senior Manager>Supervisor South>Minion 3>12>10-1>5 Jill>Fred>Frank>Carol
8 Minion 5 6 0 Mary 4 0 5 3 2 0 0 0 0 0 0 1>10>5>8 CEO>Senior Manager>Supervisor South>Minion 3>12>10-1>6 Jill>Fred>Frank>Mary
8 Minion 5 7 0 Michael 4 0 5 3 2 0 0 0 0 0 0 1>10>5>8 CEO>Senior Manager>Supervisor South>Minion 3>12>10-1>7 Jill>Fred>Frank>Michael
9 Administrator 6 8 0 Nigel 4 0 1 1 0 0 0 0 0 0 0 1>10>6>9 CEO>Senior Manager>Supervisor East>Administrator 3>12>(vacant)>8 Jill>Fred>(vacant)>Nigel
10 Senior Manager 1 12 0 Fred 2 1 1 1 0 2 1 4 2 6 3 1>10 CEO>Senior Manager 3>12 Jill>Fred
11 Supervisor South 10 11 0 Wilson 3 0 1 1 0 0 0 0 0 0 0 1>10>11 CEO>Senior Manager>Supervisor South 3>12>11 Jill>Fred>Wilson
12 Supervisor West 2 10 0 Frank 3 1 1 1 0 2 0 0 0 2 0 1>2>12 CEO>Senior Manager>Supervisor West 3>1>10 Jill>Tom>Frank
13 Executive Mid-West 1 NULL NULL NULL 2 0 1 0 1 0 0 0 0 0 0 1>13 CEO>Executive Mid-West 3>(vacant) Jill>(vacant)
Technical requirements
reportsto_position_id contains a manager's position_id, NULL for the top position.position_id must always exist, but can be vacant.position_id (and max_incumbents=1) for the tree to work properly.position_id to maintain the reporting structure. This is because the reportsto_position_id is defined for each node in the tree.employee_id can exist on multiple nodes, indicating than the employee has multiple jobs in the organisation. If an employee has 1 job, their multi_job_sequence will be 0. If an employee has multiple jobs, their multi_job_sequence is incremented.max_incumbents to cap the amount of employees allowed to fill that position. Vacancies do not have job rows, but can be calculated.Span of Control report business requirements
The report must answer the following questions, which are common in hierarchical organisations:
CEO>Senior Manager>Supervisor South>Minion, or 1>2>5>8?Jill>Tom>Frank>Olivia or 3>1>10-1>4?Sample data
position table
position_id descr reportsto_position_id max_incumbents
1 CEO NULL 1
2 Senior Manager 1 1
3 West Winger 2 2
4 Executive Assistant 1 1
5 Supervisor South 10 1
6 Supervisor East 10 1
7 Expert 12 2
8 Minion 5 5
9 Administrator 6 1
10 Senior Manager 1 1
11 Supervisor South 10 1
12 Supervisor West 2 1
13 Executive Mid-West 1 1
job table
employee_id multi_job_sequence employee_name position_id
1 0 Tom 2
2 0 Doug 4
3 0 Jill 1
4 0 Olivia 7
5 0 Carol 8
6 0 Mary 8
7 0 Michael 8
8 0 Nigel 9
9 0 David 7
10 0 Frank 12
10 1 Frank 5
11 0 Wilson 11
12 0 Fred 10
SQL
-- Position incumbents. One row for each position, employee_id, multi_job_sequence combination.
with cte_incumbents
as
(
select
cp.position_id,
cp.reportsto_position_id,
cp.max_incumbents,
cj.employee_id,
cj.multi_job_sequence
from position cp
left join job cj on cj.position_id = cp.position_id
),
-- Incumbents count (filled and vacant) per position
cte_incumbents_count
as
(
select
i.reportsto_position_id,
i.position_id,
count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence)) as filled_count,
(i.max_incumbents - count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence))) as vacant_count,
i.max_incumbents
from cte_incumbents i
where i.employee_id is not null
group by i.reportsto_position_id,
i.position_id,
i.max_incumbents
UNION ALL
select
i.reportsto_position_id,
i.position_id,
0 as filled_count,
(count(*) * i.max_incumbents) as vacant_count,
i.max_incumbents
from cte_incumbents i
where i.employee_id is null
group by i.reportsto_position_id,
i.position_id,
i.max_incumbents
),
-- Count the filled and vacant reports_to positions
cte_reportsto_count
as
(
select
i.reportsto_position_id,
sum(i.filled_count) as filled_count,
sum(i.vacant_count) as vacant_count,
sum(i.max_incumbents) as total_incumbents
from cte_incumbents_count i
group by i.reportsto_position_id
),
-- Create the organisation tree, based on the reportsto_position_id
cte_reportsto_tree
as
(
select
rtt.position_id,
rtt.employee_id,
rtt.multi_job_sequence,
rtt.position_descr,
rtt.reportsto_position_id,
rtt.employee_name,
level as tree_level_num,
case when connect_by_isleaf = 0 then 1 else 0 end as is_manager,
rtt.max_incumbents,
nvl((
select
rtc.filled_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = rtt.position_id
),0) as filled_direct_reports,
nvl((
select
rtc.vacant_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = rtt.position_id
),0) as vacant_direct_reports,
substr(sys_connect_by_path(rtt.position_id,'>'),2,length(sys_connect_by_path(rtt.position_id,'>'))-1) as reporting_path_position_id,
substr(sys_connect_by_path(rtt.position_descr,'>'),2,length(sys_connect_by_path(rtt.position_descr,'>'))-1) as reporting_path_position_descr,
substr(sys_connect_by_path(nvl(case when rtt.employee_id is null then null else case when rtt.multi_job_sequence = 0 then to_char(rtt.employee_id) else rtt.employee_id || '-' || rtt.multi_job_sequence end end,'(vacant)'),'>'),2,length(sys_connect_by_path(nvl(case when rtt.employee_id is null then null else rtt.employee_id || '-' || rtt.multi_job_sequence end,'(vacant)'),'>'))-1) as reporting_path_employee,
substr(sys_connect_by_path(nvl(rtt.employee_name,'(vacant)'),'>'),2,length(sys_connect_by_path(nvl(rtt.employee_name,'(vacant)'),'>'))-1) as reporting_path_name
from
(
select
cp.position_id,
cp.descr as position_descr,
cp.max_incumbents,
cp.reportsto_position_id,
cj.employee_id,
cj.multi_job_sequence,
cj.employee_name
from position cp
left join job cj on cj.position_id = cp.position_id -- Positions may not be filled
) rtt
connect by prior rtt.position_id = rtt.reportsto_position_id
start with rtt.reportsto_position_id is null -- Start at the top of the tree
),
-- Create the report detail, traversing the tree (creating subtrees to get the indirect values). This is the tough part!
cte_report_detail
as
(
select
soc.position_id,
soc.position_descr,
soc.reportsto_position_id,
soc.employee_id,
soc.multi_job_sequence,
soc.employee_name,
soc.tree_level_num,
soc.is_manager,
soc.max_incumbents,
nvl(
(
select
ic.filled_count
from cte_incumbents_count ic
where ic.position_id = soc.position_id
),0) as filled_head_count,
nvl(
(
select
ic.vacant_count
from cte_incumbents_count ic
where ic.position_id = soc.position_id
),0) as vacant_head_count,
soc.filled_direct_reports as filled_direct_reports,
soc.vacant_direct_reports as vacant_direct_reports,
case when soc.is_manager = 1 then
-- Get the filled count of all of the positions underneath and subtract the direct reports to arrive at the filled indirect reports count
(
select
sum(
(
select
rtc.filled_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = cp.position_id
)
)
from position cp
connect by prior cp.position_id = cp.reportsto_position_id
start with cp.position_id = soc.position_id
) - soc.filled_direct_reports else 0 end as filled_indirect_reports,
-- Get the vacant count of all of the positions underneath and subtract the direct reports to arrive at the vacant indirect reports count
case when soc.is_manager = 1 then
(
select
sum(
(
select
rtc.vacant_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = cp.position_id
)
)
from position cp
connect by prior cp.position_id = cp.reportsto_position_id
start with cp.position_id = soc.position_id
) - soc.vacant_direct_reports else 0 end as vacant_indirect_reports,
to_clob(cast(soc.reporting_path_position_id as varchar2(4000))) as reporting_path_position_id,
to_clob(cast(soc.reporting_path_position_descr as varchar2(4000))) as reporting_path_position_descr,
to_clob(cast(soc.reporting_path_employee as varchar2(4000))) as reporting_path_employee,
to_clob(cast(soc.reporting_path_name as varchar2(4000))) as reporting_path_employee_name
from cte_reportsto_tree soc
)
-- Final calculations and sort
select
r.position_id,
r.position_descr,
r.reportsto_position_id,
r.employee_id,
r.multi_job_sequence,
r.employee_name,
r.tree_level_num,
r.is_manager,
r.max_incumbents,
r.filled_head_count,
r.vacant_head_count,
r.filled_direct_reports,
r.vacant_direct_reports,
r.filled_indirect_reports,
r.vacant_indirect_reports,
(r.filled_direct_reports + r.filled_indirect_reports) as employees_under_position,
(r.vacant_direct_reports + r.vacant_indirect_reports) as vacancies_under_position,
r.reporting_path_position_id,
r.reporting_path_position_descr,
r.reporting_path_employee,
r.reporting_path_employee_name
from cte_report_detail r
order by r.position_id,
r.employee_id,
r.multi_job_sequence;
After a bit of work, I managed to answer my own question to reproduce exactly the same result using CTEs.
The recursive CTEs feature works in Oracle in this situation, with a few restrictions. Another database would need to support recursion with DEPTH FIRST searching, along with analytical functions.
Theoretically, this code could be ported across with minor changes to the syntax.
Key points/lessons learnt:
ORA-01702: a view is not appropriate here.SEARCH DEPTH FIRST BY reportsto_position_id SET seq clause was important to set is_manager, with help from the LEAD() analytical function.reporting_path fields was useful for traversing it correctly. I used the INSTR() function to ensure a position existed in a given path.Sample data tables and basic counts
-- Create a table for each current position.
create table position
(
position_id NUMBER(11) NOT NULL,
descr VARCHAR2(50) NOT NULL,
reportsto_position_id NUMBER(11),
max_incumbents NUMBER(4) NOT NULL
);
create unique index position_idx1 on position (position_id);
-- Create a table to store the current job data.
create table job
(
employee_id NUMBER(11) NOT NULL,
multi_job_sequence NUMBER(1) NOT NULL,
employee_name VARCHAR2(50) NOT NULL,
position_id NUMBER(11) NOT NULL
);
create unique index job_idx1 on job (employee_id, multi_job_sequence);
create index job_idx2 on job (position_id, employee_id, multi_job_sequence);
-- Insert data into position table
insert into position values (1, 'CEO', NULL, 1);
insert into position values (2, 'Senior Manager', 1, 1);
insert into position values (3, 'West Winger', 2, 2);
insert into position values (4, 'Executive Assistant', 1, 1);
insert into position values (5, 'Supervisor South', 10, 1);
insert into position values (6, 'Supervisor East', 10, 1);
insert into position values (7, 'Expert', 12, 2);
insert into position values (8, 'Minion', 5, 5);
insert into position values (9, 'Administrator', 6, 1);
insert into position values (10, 'Senior Manager', 1, 1);
insert into position values (11, 'Supervisor South', 10, 1);
insert into position values (12, 'Supervisor West', 2, 1);
insert into position values (13, 'Executive Mid-West', 1, 1);
commit;
-- Insert data into job table
insert into job values (1, 0, 'Tom', 2);
insert into job values (2, 0, 'Doug', 4);
insert into job values (3, 0, 'Jill', 1);
insert into job values (4, 0, 'Olivia', 7);
insert into job values (5, 0, 'Carol', 8);
insert into job values (6, 0, 'Mary', 8);
insert into job values (7, 0, 'Michael', 8);
insert into job values (8, 0, 'Nigel', 9);
insert into job values (9, 0, 'David', 7);
insert into job values (10, 0, 'Frank', 12);
insert into job values (10, 1, 'Frank', 5);
insert into job values (11, 0, 'Wilson', 11);
insert into job values (12, 0, 'Fred', 10);
commit;
-- Build up the tables
-- Position incumbents. One row for each position, employee_id, multi_job_sequence combination.
create table cte_incumbents
as
(
select
cp.position_id,
cp.reportsto_position_id,
cp.max_incumbents,
cj.employee_id,
cj.multi_job_sequence
from position cp
left join job cj on cj.position_id = cp.position_id
);
create unique index cte_incumbents_idx1 on cte_incumbents (position_id, employee_id, multi_job_sequence);
create index cte_incumbents_idx2 on cte_incumbents (position_id, reportsto_position_id);
-- Incumbents count (filled and vacant) per position
create table cte_incumbents_count
as
(
select
i.reportsto_position_id,
i.position_id,
count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence)) as filled_count,
(i.max_incumbents - count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence))) as vacant_count,
i.max_incumbents
from cte_incumbents i
where i.employee_id is not null
group by i.reportsto_position_id,
i.position_id,
i.max_incumbents
UNION ALL
select
i.reportsto_position_id,
i.position_id,
0 as filled_count,
(count(*) * i.max_incumbents) as vacant_count,
i.max_incumbents
from cte_incumbents i
where i.employee_id is null
group by i.reportsto_position_id,
i.position_id,
i.max_incumbents
);
create unique index cte_incumbents_count_idx on cte_incumbents_count (reportsto_position_id, position_id);
-- Count the filled and vacant reports_to positions
create table cte_reportsto_count
as
(
select
i.reportsto_position_id,
sum(i.filled_count) as filled_count,
sum(i.vacant_count) as vacant_count,
sum(i.max_incumbents) as total_incumbents
from cte_incumbents_count i
group by i.reportsto_position_id
);
create unique index cte_reportsto_count_idx on cte_reportsto_count (reportsto_position_id);
Report using CTEs
create table cte_reportsto_tree as
-- Create the organisation tree, based on the reportsto_position_id
with cte_reportsto_tree_base (
position_id,
position_descr,
reportsto_position_id,
employee_id,
multi_job_sequence,
employee_name,
tree_level_num,
max_incumbents,
filled_direct_reports,
vacant_direct_reports,
reporting_path_position_id,
reporting_path_position_descr,
reporting_path_employee,
reporting_path_employee_name
)
as
(
-- Anchor member
select
cp1.position_id,
cp1.descr as position_descr,
cp1.reportsto_position_id,
cj1.employee_id,
cj1.multi_job_sequence,
cj1.employee_name,
1 as tree_level_num,
cp1.max_incumbents,
nvl((
select
rtc.filled_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = cp1.position_id
),0) as filled_direct_reports,
nvl((
select
rtc.vacant_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = cp1.position_id
),0) as vacant_direct_reports,
to_char(cp1.position_id) as reporting_path_position_id,
cp1.descr as reporting_path_position_descr,
to_char(cj1.employee_id) as reporting_path_employee,
cj1.employee_name as reporting_path_employee_name
from position cp1
left join job cj1 on cj1.position_id = cp1.position_id -- Positions may not be filled
where cp1.position_id = 1 -- start at position = 1
UNION ALL
-- Recursive member
select
cp2.position_id,
cp2.descr as position_descr,
cp2.reportsto_position_id,
cj2.employee_id,
cj2.multi_job_sequence,
cj2.employee_name,
rtt.tree_level_num + 1 as tree_level_num,
cp2.max_incumbents,
nvl((
select
rtc.filled_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = cp2.position_id
),0) as filled_direct_reports,
nvl((
select
rtc.vacant_count
from cte_reportsto_count rtc
where rtc.reportsto_position_id = cp2.position_id
),0) as vacant_direct_reports,
rtt.reporting_path_position_id || '>' || to_char(cp2.position_id) as reporting_path_position_id,
rtt.reporting_path_position_descr || '>' || cp2.descr as reporting_path_position_descr,
rtt.reporting_path_employee || '>' || nvl(case when cj2.employee_id is null then null else case when cj2.multi_job_sequence = 0 then to_char(cj2.employee_id) else to_char(cj2.employee_id) || '-' || to_char(cj2.multi_job_sequence) end end,'(vacant)') as reporting_path_employee,
rtt.reporting_path_employee_name || '>' || nvl(cj2.employee_name,'(vacant)') as reporting_path_employee_name
from position cp2
inner join cte_reportsto_tree_base rtt on rtt.position_id = cp2.reportsto_position_id
left join job cj2 on cj2.position_id = cp2.position_id -- Positions may not be filled
)
SEARCH DEPTH FIRST BY reportsto_position_id SET seq
select
rtt.position_id,
rtt.position_descr,
rtt.reportsto_position_id,
rtt.employee_id,
rtt.multi_job_sequence,
rtt.employee_name,
rtt.tree_level_num,
rtt.max_incumbents,
rtt.filled_direct_reports,
rtt.vacant_direct_reports,
rtt.reporting_path_position_id,
rtt.reporting_path_position_descr,
rtt.reporting_path_employee,
rtt.reporting_path_employee_name,
case when (rtt.tree_level_num - lead(rtt.tree_level_num) over (order by seq)) < 0 then 1 else 0 end is_manager -- Is a manager if there is a difference between levels on the tree.
from cte_reportsto_tree_base rtt;
create index cte_reportsto_tree_idx on cte_reportsto_tree (position_id, reportsto_position_id, employee_id, multi_job_sequence);
create table cte_fir as
(
select
soc.position_id,
soc.is_manager,
soc.tree_level_num,
soc.filled_direct_reports,
soc.vacant_direct_reports,
case when soc.is_manager = 1 then
nvl((
select
sum(ind.filled_direct_reports)
from cte_reportsto_tree ind
where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
),0)
else 0 end as filled_indirect_reports,
case when soc.is_manager = 1 then
nvl((
select
sum(ind.vacant_direct_reports)
from cte_reportsto_tree ind
where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
),0)
else 0 end as vacant_indirect_reports
from cte_reportsto_tree soc
where soc.tree_level_num = 1
UNION ALL
select
soc.position_id,
soc.is_manager,
soc.tree_level_num,
soc.filled_direct_reports,
soc.vacant_direct_reports,
case when soc.is_manager = 1 then
nvl((
select
sum(ind.filled_direct_reports)
from cte_reportsto_tree ind
where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
and instr(ind.reporting_path_position_id, '>'|| soc.position_id || '>') > 0 -- The position must be in the flattened tree path (quick way of traversing the tree)
),0)
else 0 end as filled_indirect_reports,
case when soc.is_manager = 1 then
nvl((
select
sum(ind.vacant_direct_reports)
from cte_reportsto_tree ind
where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
and instr(ind.reporting_path_position_id, '>'|| soc.position_id ||'>') > 0 -- The position must be in the flattened tree path (quick way of traversing the tree)
),0)
else 0 end as vacant_indirect_reports
from cte_reportsto_tree soc
where soc.tree_level_num > 1
);
create index cte_fir_idx on cte_fir (position_id);
select
soc.position_id,
soc.position_descr,
soc.reportsto_position_id,
soc.employee_id,
soc.multi_job_sequence,
soc.employee_name,
soc.tree_level_num,
soc.is_manager,
soc.max_incumbents,
nvl(
(
select
ic.filled_count
from cte_incumbents_count ic
where ic.position_id = soc.position_id
),0) as filled_head_count,
nvl(
(
select
ic.vacant_count
from cte_incumbents_count ic
where ic.position_id = soc.position_id
),0) as vacant_head_count,
soc.filled_direct_reports as filled_direct_reports,
soc.vacant_direct_reports as vacant_direct_reports,
(
select
sum(fir.filled_indirect_reports)
from cte_fir fir
where fir.position_id = soc.position_id
) as filled_indirect_reports,
(
select
sum(fir.vacant_indirect_reports)
from cte_fir fir
where fir.position_id = soc.position_id
) as vacant_indirect_reports,
(
select
sum(fir.filled_indirect_reports)
from cte_fir fir
where fir.position_id = soc.position_id
) + soc.filled_direct_reports as employees_under_position,
(
select
sum(fir.vacant_indirect_reports)
from cte_fir fir
where fir.position_id = soc.position_id
) + soc.vacant_direct_reports as vacancies_under_position,
soc.reporting_path_position_id,
soc.reporting_path_position_descr,
soc.reporting_path_employee,
soc.reporting_path_employee_name
from cte_reportsto_tree soc
order by soc.position_id,
soc.employee_id,
soc.multi_job_sequence;