BAQ no results for one user

Epicor ERP 10
1

I have one user who gets no results and no errors when querying the Erp.Customer table in a BAQ

Have got him to write a BAQ with just the Customer table in it as a test. Again no results

Is this related to Sales territory authorisation somehow?

thanks,

2

That’s because of territory security./

2 Likes
3

That’s what i thought, however i have tried setting ‘SalesRep.ViewAllTer’ for that user and it doesnt seem to work.

4

Is that user an authorized user on more than one rep?

5

They are just an authorised user of their own salerep code :smiley:

6

And that Sales Rep code is set to View All Territories?.. hmmm are you absolutely sure they aren’t authorized in a different sales rep?

7

I have found that once you set an authorized user for Territory then you must set all. So remove their User ID from their own record but leave the View All and see if that works. Otherwise, add them them as an authorized user on every territory.

8

Thanks Mark. I will try it both ways to see if I can recreate the problem with a test user and then ‘fix’ that user

I started trying to work through the BAQ sqlplan to determine the source of the problem…arrrgh.

Richard.

9

So this problem just popped up for me on a dashboard that was allowing all users to view all customers and territories.

We have no erp.SaleAuth records and our company config Territory Security on Sales Orders is false.

Also, all erp.UserComp.PrimSalesRepID are null.

Any ideas of what other security could of changed to remove BAQ access to Customers and Territories? Thanks,

10

Do a BAQ on ZDataTable, and, separately, one for zDataField. They should tell you what is restricted by territory or plant.

11

I can see the tables were the territory restriction would apply but isn’t that only set at the company level? Meaning, if the company level is false then those tables don’t have security?

12

Ah, that’s over my head. I have not dug that deep with it. Once I got the answer I needed I just filed this away for later use.

13

What I do not understand is that we do not have this behavior in our older Pilot system, yet if I grab the query from SQL Profiler and run it in both databases they both return the same dataset in SSMS???

Here is a copy of the query if you ever want to join run it and see what the user has access too or if you want to join it to the generated BAQ SQL to see how it would work. You can just replace the "@TerritoryLst " variable with the temp table “#TerritoryLst” referenced below.

DECLARE @Company varchar(8) = ''
DECLARE @CurrentUserID varchar(50) = ''

IF OBJECT_ID('tempdb..#TerritoryLst') IS NOT NULL DROP TABLE #TerritoryLst;



with [_TerrCTE_SalesRep]  as 
(
    select uc.Company as Company, sr.SalesRepCode, ISNull(sr.ViewAllTer,0) as ViewAllTer, (case when em.FeatureID is null then 0 else 1 end) as TrCrAllowed 
	    from [Erp].[UserComp] uc
        left join [Erp].[SaleAuth] sa on sa.Company = uc.Company and sa.DcdUserID = uc.DcdUserID
        left join [Erp].[SalesRep] sr on sr.Company = uc.Company  and sr.SalesRepCode = sa.SalesRepCode
        left join [Ice].[SysCompany] sc on sc.Company = uc.Company
        left join [Ice].[EnabledModule] em on em.InstallationID = sc.InstallationID and em.FeatureID = '00000003-9662-4B1D-AD67-8D90C3599092'
    where
        (uc.Company = @Company) and uc.DcdUserID = @CurrentUserID
)


select st.Company, st.TerritoryID -- CRM disabled
into #TerritoryLst
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st on st.Company = [_TerrCTE_SalesRep].Company and st.Inactive = 0
where [_TerrCTE_SalesRep].TrCrAllowed = 0

union

select st1.Company, st1.TerritoryID -- CRM enabled and ViewAllTerr=1
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st1 on st1.Company = [_TerrCTE_SalesRep].Company and st1.Inactive = 0
where [_TerrCTE_SalesRep].ViewAllTer = 1 and [_TerrCTE_SalesRep].TrCrAllowed = 1

union

select st2.Company, st2.TerritoryID -- CRM enabled and no SaleAuth
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st2 on st2.Company = [_TerrCTE_SalesRep].Company and st2.Inactive = 0
where [_TerrCTE_SalesRep].TrCrAllowed = 1 and not exists(select top 1 1 from [Erp].[SaleAuth] sa2 
where sa2.Company = [_TerrCTE_SalesRep].Company and sa2.DcdUserID = @CurrentUserID
and sa2.SalesRepCode = [_TerrCTE_SalesRep].SalesRepCode)

union

select strp.Company, strp.TerritoryID -- CRM enabled and ViewAllTerr=0 and territories assigned
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTRP] strp on [_TerrCTE_SalesRep].ViewAllTer = 0 
and [_TerrCTE_SalesRep].Company = strp.Company and [_TerrCTE_SalesRep].SalesRepCode = strp.SalesRepCode;
 
select * from #TerritoryLst