• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Business Rule to Delete Unused Departments
#1
We're trying to configure a business rule that will delete unused departments when a department no longer has any employees within it, however I do not see a way to achieve that. 

In Operation > Directory > Departments > Select a Department > How is evSM calculating the number of employees in AM_DEPARTMENT if only AM_EMPLOYEE has DEPARTMENT_ID.

Ideally, I was hoping to create a business rule based on some logic where if Employees for that Department are zero, then delete it or close, but I am stuck on how-to find a matching criteria.
jpbyanks178, proud to be a member of EV CONNECT FORUM since Jun 2018.

#2
(07-30-2018, 01:23 PM)jpbyanks178 Wrote: We're trying to configure a business rule that will delete unused departments when a department no longer has any employees within it, however I do not see a way to achieve that. 

In Operation > Directory > Departments > Select a Department > How is evSM calculating the number of employees in AM_DEPARTMENT if only AM_EMPLOYEE has DEPARTMENT_ID.

Ideally, I was hoping to create a business rule based on some logic where if Employees for that Department are zero, then delete it or close, but I am stuck on how-to find a matching criteria.

Hello,
You can do something like : 

Code:
SELECT     DEPARTMENT_FR
FROM         (SELECT     COUNT([40000].AM_EMPLOYEE.EMPLOYEE_ID) AS NbEmployee, [40000].AM_DEPARTMENT.DEPARTMENT_FR
                      FROM          [40000].AM_DEPARTMENT LEFT OUTER JOIN
                                             [40000].AM_EMPLOYEE ON [40000].AM_DEPARTMENT.DEPARTMENT_ID = [40000].AM_EMPLOYEE.DEPARTMENT_ID
                      GROUP BY [40000].AM_DEPARTMENT.DEPARTMENT_FR) AS L
WHERE     (NbEmployee = 0)

Prefere close departments rather than delete because you could have request, equipment, contract,... 

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#3
(08-01-2018, 04:59 AM)ABBE Philippe Wrote:
(07-30-2018, 01:23 PM)jpbyanks178 Wrote: We're trying to configure a business rule that will delete unused departments when a department no longer has any employees within it, however I do not see a way to achieve that. 

In Operation > Directory > Departments > Select a Department > How is evSM calculating the number of employees in AM_DEPARTMENT if only AM_EMPLOYEE has DEPARTMENT_ID.

Ideally, I was hoping to create a business rule based on some logic where if Employees for that Department are zero, then delete it or close, but I am stuck on how-to find a matching criteria.

Hello,
You can do something like : 

Code:
SELECT     DEPARTMENT_FR
FROM         (SELECT     COUNT([40000].AM_EMPLOYEE.EMPLOYEE_ID) AS NbEmployee, [40000].AM_DEPARTMENT.DEPARTMENT_FR
                      FROM          [40000].AM_DEPARTMENT LEFT OUTER JOIN
                                             [40000].AM_EMPLOYEE ON [40000].AM_DEPARTMENT.DEPARTMENT_ID = [40000].AM_EMPLOYEE.DEPARTMENT_ID
                      GROUP BY [40000].AM_DEPARTMENT.DEPARTMENT_FR) AS L
WHERE     (NbEmployee = 0)

Prefere close departments rather than delete because you could have request, equipment, contract,... 

P.ABBE

Hi Phillipe,

How does this work with the Department's nested structure?
ie most parent departments don't have any employees in there to start with, and you cannot/shouldn't remove or close a parent department until all child departments have no employees in them as well.

This is something I have struggled with, and have no solution for at this stage.
Wayne Smith, Charles Darwin University, Australia
proud to be a member of EV CONNECT FORUM since Jun 2016.

#4
(08-02-2018, 11:27 PM)wsmith Wrote: ie most parent departments don't have any employees in there to start with, and you cannot/shouldn't remove or close a parent department until all child departments have no employees in them as well.

This is something I have struggled with, and have no solution for at this stage.

Hello
Consider the company AEF (DEMO 40000) which have the following entity tree structure :
LEVEL DEPARTMENT_EN
2 National Sales
3 National Sales/Direct
4 National Sales/Direct/Agriculture
4 National Sales/Direct/Chemical
4 National Sales/Direct/Construction
3 National Sales/Indirect
4 National Sales/Indirect/Affiliates

It is possible that there is no employee on levels 2 and 3 so the previous request becomes:

Code:
SELECT     DEPARTMENT_FR
FROM         (SELECT     COUNT([40000].AM_EMPLOYEE.EMPLOYEE_ID) AS NbEmployee, [40000].AM_DEPARTMENT.DEPARTMENT_FR
                     FROM          [40000].AM_DEPARTMENT LEFT OUTER JOIN
                                            [40000].AM_EMPLOYEE ON [40000].AM_DEPARTMENT.DEPARTMENT_ID = [40000].AM_EMPLOYEE.DEPARTMENT_ID
                                            where LEVEL in (4)
                     GROUP BY [40000].AM_DEPARTMENT.DEPARTMENT_FR) AS L
WHERE     (NbEmployee = 0)

You should also know that setting an end date manually (or in SQL) on an entity has no consequence on child entities as well as on related hardware and employees; the entity will always be visible from the directory provided that at least one child entity remains open. The only consequence is that during a move (or other wizards) the entities with an end date are no longer visible.

For the purpose of automation, it is possible to :
- Set an end date when the entity (or location) has not been updated for X days (depends on how often the directory is updated)
- Set an end date to all entities before importing the repository by forcing at this time its end date (at a later date ex : 2021)

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#5
If you would like to calculate the number of employees attached to a department and all of its sub-departments, you can use this query. The derived query gets the number of employees directly attached to a department and then there's an aggregation to get the total. The join formula uses LFT, RGT and LVL to retrieve all child departments and itself for a given department. Order by level DESC to delete the most deep departments first.

I included the path so you can check what the query is returning.

You need to do something similar with AM_ASSET before deleting or hiding a department unless you don't assign assets to departments.


Code:
SELECT AM_DEPARTMENT.DEPARTMENT_ID, AM_DEPARTMENT."LEVEL",
AM_DEPARTMENT_PATH.DEPARTMENT_PATH_FR, SUM(T1.NBEMP ) NBEMPTOTAL
FROM [50004].AM_DEPARTMENT INNER JOIN
(SELECT AM_DEPARTMENT.DEPARTMENT_ID, AM_DEPARTMENT.LFT, AM_DEPARTMENT.RGT,
AM_DEPARTMENT."LEVEL" LVL, COUNT(AM_EMPLOYEE.EMPLOYEE_ID) NBEMP
FROM [50004].AM_DEPARTMENT
LEFT OUTER JOIN [50004].AM_EMPLOYEE
ON AM_DEPARTMENT.DEPARTMENT_ID = AM_EMPLOYEE.DEPARTMENT_ID
GROUP BY AM_DEPARTMENT.DEPARTMENT_ID, AM_DEPARTMENT.LFT, AM_DEPARTMENT.RGT,
AM_DEPARTMENT."LEVEL") AS T1
ON AM_DEPARTMENT.LFT <= T1.LFT AND AM_DEPARTMENT.RGT >= T1.RGT AND AM_DEPARTMENT."LEVEL" <= T1.LVL
INNER JOIN [50004].AM_DEPARTMENT_PATH
ON AM_DEPARTMENT.DEPARTMENT_ID = AM_DEPARTMENT_PATH.DEPARTMENT_ID
GROUP BY AM_DEPARTMENT.DEPARTMENT_ID, AM_DEPARTMENT_PATH.DEPARTMENT_PATH_FR, "LEVEL"
ORDER BY LEVEL DESC, AM_DEPARTMENT.DEPARTMENT_ID
Amine.






Users browsing this thread: 1 Guest(s)