User Sales Hierarchy vs Outlet Assignment Validation

Description

Validates that users are assigned to the correct sales hierarchies and associated outlets.


Query :

select B.ClientID,C.UserId,Username,/*A.CreatedOn,A.ModifiedOn,LocationCluster,*/A.SalesHierarchyId,E.Name,E.Code,LevelName,SalesHierarchyType,/*B.LocationId,D.LocationId,B.Code CustomerCode*/
	COUNT(case when LocationCluster = 'On-premise' then 'On-premise' end) as 'On-Premise',
	COUNT(case when LocationCluster = 'Traditional' then 'Traditional' end) as 'Traditional',
	--COUNT(case when LocationCluster = 'Modern Trade' then 'Modern Trade' end) as 'Modern Trade',
	COUNT(Distinct D.LocationId)Total_count
	--COUNT(Distinct C.UserId)Total_count
	from UserSalesHierarchy A
	join Security_User C with (nolock) on A.UserId=C.UserId and ClientID = 408 and A.IsDeleted = 0 and C.IsDeleted = 0-- and UPN IN (Select UPN from ##UPNs)
	join LocationSalesHierarchy D with (nolock) on D.SalesHierarchyId=A.SalesHierarchyId
	join vwLocationList B with (nolock) on D.LocationId=B.LocationId --and B.ClientID = 71 
	join saleshierarchy E with (nolock) on A.SalesHierarchyId=E.SalesHierarchyId
	full outer join vwsaleshierarchylist F with (nolock) on A.SalesHierarchyId=F.SalesHierarchyId
	where E.Code IN (
	'AC0063'
	)
	and B.IsActive = 1 
	and convert(char,convert(datetime,B.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120) >= '2025-10-21'
	group by B.ClientID,C.UserId,Username,/*A.CreatedOn,A.ModifiedOn,LocationCluster,*/A.SalesHierarchyId,E.Name,E.Code,LevelName,SalesHierarchyType
	order by B.ClientID,C.UserId,A.SalesHierarchyID--,Name,Code