Row Level security information gets stored in a complicated format in the Acumatica database so it's hard to report on with SQL.

Here are two SQL Views that can help you to untangle the mystery:

CREATE OR ALTER VIEW dbo.RDPViewsUserRelationGroups AS
select
      U.CompanyID,
      U.Username,
      RG.GroupName,
      RG.Description
from Users U
      left outer join RelationGroup RG on RG.CompanyID = U.CompanyID and (
            CONVERT(BIGINT, substring(RG.GroupMask, 1, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 1, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 5, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 5, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 9, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 9, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 13, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 13, 4)) != 0
      )

CREATE OR ALTER VIEW dbo.RDPViewsCustomerRelationGroups AS
select
      BA.CompanyID,
      BA.BAccountID,
      BA.AcctCD,
      BA.AcctName,
      RG.GroupName,
      RG.Description
from Customer C
      inner join BAccount BA on BA.CompanyID = C.CompanyID and BA.BAccountID = C.BAccountID
      left outer join RelationGroup RG on RG.CompanyID = C.CompanyID and (
            CONVERT(BIGINT, substring(RG.GroupMask, 1, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 1, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 5, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 5, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 9, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 9, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 13, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 13, 4)) != 0
      )

Here's an example from an actual AugSQL User.

They can successfully run the following SQL Code without any problems:

SELECT *
FROM CustomerClass

But, when they try to add a WHERE clause like this:

SELECT *
FROM CustomerClass
WHERE CompanyID=2

They get this error message

ErrorCode: 406.

For this particular AugSQL User, they get the error message in Acumatica Version 24.105.0036, but they don't get the error (running the same SQL code) in Acumatica Version 23.207.0025.

But we couldn't reproduce the error in a local Acumatica 24.105.0036 environment.

So, we're not sure at this point if it's an Acumatica Version problem or a SQL Version problem.

If you get the ErrorCode: 406. error, please reply to this Discussion Topic with both the Acumatica Version that you're running (ie. 24.105.0036) and the SQL Version that you're running. You can find the SQL Version that you're running by running the following SQL command:

SELECT @@Version 'SQL Version'

You can simply copy and paste the gigantic string that you see and reply to this Discussion Topic with it. It will be something like this:

Microsoft SQL Server 2019 (RTM-GDR) (KB5040986) - 15.0.2116.2 (X64) Jul 2 2024 17:41:57 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )

The good news is that:

1. So far, this is the only AugSQL bug that we've run into.

2. There is a workaround!

The workaround is to add the TOP command. If you want to return all records, just pick a really big number that will be larger than the number of records returned.

Something like this:

SELECT TOP(999999) *
FROM CustomerClass
WHERE CompanyID=2

Previously, I would use this SQL code to create a VIEW:

DROP VIEW IF EXISTS dbo.MySQLSystemEvent
GO
CREATE VIEW dbo.MySQLSystemEvent AS
SELECT 2 AS CompanyID,ID,TenantName,,,Source,EventID,ScreenID,,Details,Properties
FROM SystemEvent

The reason for the first two lines is that I want to be able to run the same SQL Code whether the View already exists or not.

But there are two problems with it:

1. I can never remember the DROP VIEW IF EXISTS... part and always have to Google it.

2. The view gets a new Created date on it which I don't like. I'd like to know the date that the view was originally created.

Now, with my new discovery (that has existed for 8 year already apparently), I can write this instead:

CREATE OR ALTER VIEW dbo.MySQLSystemEvent AS
SELECT 2 AS CompanyID,ID,TenantName,,,Source,EventID,ScreenID,,Details,Properties
FROM SystemEvent

This is awesome! It solves both problems:

1. I can remember CREATE OR ALTER without having to Google it.

2. The Created date on the view doesn't change since an ALTER statement gets run if it already exists.

I love it!

I discovered this at a client that had recently upgraded from Acumatica 2022 R1 to Acumatica 2023 R2. The QtyOnHand numbers looked funny to them and it turned out that the problem was with any SQL Views that were using INSiteStatus.QtyOnHand.

The Primary Keys for INSiteStatus are CompanyID,InventoryID,SiteID,SubItemID

The Primary Keys for INItemStats are CompanyID,InventoryID,SiteID

I like using INItemStats better because SubItemID is related to Sub Items which are rarely used (for good reason) in Acumatica implementations.

If I do a Trace on a Generic Inquiry in Acumatica 2022 R1 that uses INSiteStatus, I get something like this:

SELECT COUNT( *)
FROM  
WHERE ( . = 2)

If I do a Trace on a Generic Inquiry in Acumatica 2023 R2 that uses INSiteStatus, I get something like this:

SELECT COUNT( *)
FROM  
WHERE ( . = 2) AND ( . = 0)

So you can see that, in Acumatica 2023 R2, INSiteStatus is now pulling its data from INSiteStatusByCostCenter for some reason. I'm not sure why, but it is.

The Primary Keys for INSiteStatusByCostCenter are CompanyID,InventoryID,SiteID,SubItemID,CostCenterID

I prefer using INItemStats over over INSiteStatusByCostCenter because its Primary Keys are simpler.

1. LEFT JOIN
2. RIGHT JOIN
3. INNER JOIN
4. FULL JOIN
5. CROSS JOIN

I personally pretty much always use either LEFT JOIN or INNER JOIN so I'm just going to talk about these 2 join types in this post.

But what's the difference between LEFT JOIN and INNER JOIN?

First, as a rule, I like to start with the table that is the most detailed, then I join to additional tables to get additional information about a record in the first table.

For example, if we're looking at Customers and we want to get the description of their default payment method, we would start with the Customers table, then join to the PaymentMethod table.

Let's start with this query to see a list of Customers and the ID of each customer's default payment method:

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID, C.DefPaymentMethodID
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2
ORDER BY B.AcctCD

Notice in the previous screenshot that there are 126 customers and 2 of the customers in the screenshot have an empty DefPaymentMethodID.

Let's make sure that those are the only 2 customers with an empty DefPaymentMethodID value with this query where we check for a NULL (empty) DefPaymentMethodID:

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID, C.DefPaymentMethodID
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2 AND C.DefPaymentMethodID IS NULL
ORDER BY B.AcctCD

Yep, looks like there are only 2 customers with a NULL (empty) DefPaymentMethodID.

Let's check the first customer (ABARTENDE) to make sure that it really doesn't have a default payment method:

Yep, no default payment method.

Ok, now, back to LEFT JOIN vs. INNER JOIN.

If we do a LEFT JOIN to the PaymentMethod table so we can get the Payment Method description from the PaymentMethod.Descr field, then we'll still get all 126 customer records, but we'll get NULL (empty) values in the PaymentMethod.Descr field for customers that don't have a default payment method.

Note the results in the following query. There are still 126 records and those same 2 customers don't have values in the PaymentMethod.Descr field.

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID,
	C.DefPaymentMethodID, P.Descr
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
LEFT JOIN PaymentMethod P
	ON C.CompanyID=P.CompanyID
	AND C.DefPaymentMethodID=P.PaymentMethodID
WHERE C.CompanyID=2
ORDER BY B.AcctCD

But, if we change LEFT JOIN to INNER JOIN when joining to PaymentMethod like this:

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID,
	C.DefPaymentMethodID, P.Descr
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
INNER JOIN PaymentMethod P
	ON C.CompanyID=P.CompanyID
	AND C.DefPaymentMethodID=P.PaymentMethodID
WHERE C.CompanyID=2
ORDER BY B.AcctCD

Then we can see that only 124 records are returned and the 2 customers that don't have a default payment method aren't included in the results, that's why there are only 124 records (126 minus 2).

So, what's the difference between LEFT JOIN and INNER JOIN?

LEFT JOIN return all records from the first table, regardless of whether or not they have a match in the second table.

INNER JOIN only returns records that exist in both the first table and the second table.

In general, when doing a join, you want to include all of the columns that are in the primary key of the table that you are joining to.

When you look at the Customer Data Access Class (DAC) in Acumatica, it automatically gets the Customer ID and Customer Name for you.

In the database though, Customer ID and Customer Name are in the BAccount table which stores IDs and Names for Customers, Vendors, Employees, and Branches.

Since Customer ID and Customer Name are in the BAccount table, we need to join from Customer to BAccount to get that info.

Since BAccount is the table that we're joining to, then we need to know which columns are in the primary key.

We can find out which columns are in the primary keys by looking at the COLUMN_NAME column in the following query:

sp_pkeys 'BAccount'

As you can see in the previous screenshot, CompanyID and BAccountID are in the primary key, so we need to include both of those columns when joining to the BAccount table.

Spoiler alert: CompanyID is pretty much always in the primary key, just like we pretty much always need to include CompanyID when querying the database as discussed in this post:

https://www.augforums.com/forums/augsql/your-first-select-statement-and-always-using-companyid

I'm going to start from the Customer table and join to the BAccount table using a LEFT JOIN. For the first column, we need to use the ON statement, then use the AND statement for every column after that.

I like putting each column on a separate line because I think it's easier to read that way. But you can put them all on one line if you want.

Also, when doing joins, we then need to reference the table whenever we're referencing a column from that table. In order to make it less "wordy" so we don't have to repeat long table names every time, we can put a short alias after each table. I try to use just one letter whenever possible to make the alias as short as possible. I'll use C for Customer and B for BAccount.

SELECT *
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2

SELECT * means that we're getting all columns from both tables. If we only want the AcctCD and AcctName columns from the BAccount table, but all columns from the Customer table, then we can change SELECT * to SELECT B.AcctCD, B.AcctName, C.* like this:

SELECT B.AcctCD, B.AcctName, C.*
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2

We can also alias the column names. Since AcctCD is Customer ID and AcctName is Customer Name, we can alias them on the first line of the SELECT like this:

SELECT B.AcctCD 'Customer ID', B.AcctName 'Customer Name', C.*
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2

SELECT *
FROM Company

If that was your first SQL statement, then congratulations!

The reason why I like using the Company table in the first SQL statement is that it's great prep for pretty much every other SQL statement that you'll run.

Why? Because you should pretty much always include CompanyID (1, 2, 3 in the previous screenshot) when running a query to restrict the data returned to a specific Acumatica Tenant.

Wait, Tenant? Then why isn't it called TenantID instead of CompanyID?

Well, first we need to make sure that we're clear about what a Tenant is in Acumatica.

You can see the Tenant in the drop down on the login screen (1st screenshot) or in the upper right-hand corner of the screen after you've logged into Acumatica (2nd screenshot).

Why isn't it TenantID in the database? The Tenant in Acumatica used to be called Company, but they renamed it to Tenant in Acumatica 2018 R1. I noted it here:

https://www.augforums.com/forums/everything-else/instance-vs-tenant-vs-company-vs-branch

Even though Acumatica now calls it Tenant on the front-end, they never changed CompanyID to TenantID in the database because that would have been a major pain.

So, when you see CompanyID in the database, just think Tenant.

That matters because we always need to filter the results from a query by CompanyID so we only get the data from the Tenant that we're interested in.

Back in that first screenshot, there are 3 Companies (Tenants) listed:

• 1 is the template Tenant. It contains template data like a list of Countries and States, Timezones, etc. So everyone's CompanyID of 1 has the same data.
• 2 is the first Tenant in your Acumatica database. In my Acumatica demo environment, SalesDemo is the first Tenant listed.
• 3 is the next Tenant. In my Acumatica demo environment, Another Tenant is the second Tenant listed.

So, if we want to see Customer data from the SalesDemo Tenant, then we need to filter on CompanyID=2 when querying the Customer table:

SELECT *
FROM Customer
WHERE CompanyID=2

Bottom line, make sure to filter on CompanyID when using AugSQL to query your Acumatica database.

In future versions, we'd like to make the execution limit a user-defined field in a preferences screen. If you think that would be a good idea, you can vote on that idea here:

https://www.augforums.com/forums/augsql/user-defined-execution-timeout-setting

If a query exceeds 5 minutes, you'll get the following message:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

You can download AugSQL here:

AugForums.com/Lesson/Downloading-AugSQL

If you think that's a good idea, then please "like" this Discussion Topic to vote on it.