How can I find an Application User or Scheduled Export linked to a Hierarchy?

 When clients deactivate locations, they will try to remove the Location from all reporting Hierarchies so that they no longer appear on the NC Dashboard or Consolidated Reports.

This option will not be available if the Location level of the Hierarchy is linked to an Application User. The Hierarchy needs to be removed from the Application User before the Location can be removed from the Hierarchy.

In order to determine which Application User is linked to the Location, you can use the following SQL Query. The Location name is all that needs to be updated. It is case-sensitive (cut/paste from the App).

SELECT tu.user_first_name
	,tu.User_last_name
	,tu.user_id
FROM t_user tu
	,t_user_hierarchy uh
	,t_hierarchy_detail hd
	,t_hierarchy th
	,t_location tl
WHERE tu.user_pk = uh.user_pk
	AND uh.hierarchy_detail_pk = hd.hierarchy_detail_pk
	AND hd.hierarchy_pk = th.hierarchy_pk
	AND tl.location_id = hd.location_id
	AND tl.location_name = 'Location Name';

This also applies to the Logical Levels of a hierarchy. In order to determine which Application User is linked to the Logical Level, you can use the following SQL Query. The Logical Name is all that needs to be updated. It is case-sensitive (cut/paste from the App).

SELECT tu.user_first_name
	,tu.User_last_name
	,tu.user_id
FROM t_user tu
	,t_user_hierarchy uh
	,t_hierarchy_detail hd
	,t_hierarchy th
WHERE tu.user_pk = uh.user_pk
	AND uh.hierarchy_detail_pk = hd.hierarchy_detail_pk
	AND hd.hierarchy_pk = th.hierarchy_pk
	AND hd.logical_name = 'Logical Name';

To check if any users are linked to a location level of a hierarchy for Daily Alerts, you can run the following (Replace location name or logical name):

SELECT tu.user_first_name
	,tu.User_last_name
	,tu.user_id
FROM t_user tu
	,t_user_dash_alert da
	,t_hierarchy_detail hd
	,t_hierarchy th
	,t_location tl
WHERE tu.user_pk = da.user_pk
	AND da.hierarchy_detail_pk = hd.hierarchy_detail_pk
	AND hd.hierarchy_pk = th.hierarchy_pk
	AND tl.location_id = hd.location_id
	AND tl.location_name = 'Location Name'
	--and logical_name = 'Logical Name';

or

SELECT DISTINCT tu.user_id
	,tu.user_first_name
	,tu.user_last_name 
FROM t_user tu
	,t_user_dash_alert da
	,t_hierarchy_detail hd
	,t_hierarchy th
WHERE tu.user_pk = da.user_pk 
	AND da.hierarchy_detail_pk = hd.hierarchy_detail_pk 
	AND hd.hierarchy_pk = th.hierarchy_pk 
	AND hd.logical_name = 'LOGICAL LOCATION';

In order to delete an entire Hierarchy, you need to ensure that there are no Application Users or Scheduled Exports linked to the Hierarchy.

Using the Hierarchy, you can use the following query to search for Scheduled Exports. The hierarchy name will be filled in the hierarchy_type field:

select tas.atex_schedule_name, th.hierarchy_type
from t_atex_schedule tas, t_atex_schedule_extract tase, t_hierarchy_detail thd, t_hierarchy th
where tas.atex_schedule_pk = tase.atex_schedule_pk
and tase.hierarchy_detail_pk = thd.hierarchy_detail_pk
and thd.hierarchy_pk = th.hierarchy_pk
and th.hierarchy_type = 'Hierarchy Type';

If you need to find all users linked to a hierarchy:

SELECT tu.user_first_name
	,tu.User_last_name
	,tu.user_id
	,tu.app_user_flag
FROM t_user tu
	,t_user_hierarchy uh
	,t_hierarchy_detail hd
	,t_hierarchy th
WHERE tu.user_pk = uh.user_pk
	AND uh.hierarchy_detail_pk = hd.hierarchy_detail_pk
	AND hd.hierarchy_pk = th.hierarchy_pk
	AND th.hierarchy_type = 'Hierarchy Type';