Skip to main content
Solved

How to pick the current tenant Id for a Database Script?

  • 26 July 2023
  • 5 replies
  • 147 views

There are three tenants in our Acumatica Instance. For the purpose of creating a SQL view, we used the below DB Script.

create or alter view ardiscountView as
select  ROW_NUMBER() OVER(ORDER BY Usrdiscountcoden ASC) row,RefNbr,TranType type,Usrdiscountcoden discount,sum(CuryDiscAmt) amount from artran where Usrdiscountcoden is not null
and CompanyID=2
group by RefNbr,TranType,Usrdiscountcoden

But in here we had to hardcode the CompanyID. But we want dynamically pick the CompanyID depending on the current tenant. If you can provide a way to perform this it id highly appreciated.  

5 replies

dcomerford
Rank
Userlevel 7
Badge +14

I would like to know if it can be part of SQL script too but i suspect not so you could if you are creating a DAC and  GI use a condition in the GI on the relations tab to select the tenant so performance of the GI is not affected

Dermot Comerford
D
Rank
Userlevel 7
Badge +5

You’ll want to include a column called CompanyID in your view and then Acumatica will automatically apply the filter you want.

Naveen Boga
Rank
Userlevel 7
Badge +19

@malinthawarnakulasooriya08   As you are creating the DAC from the SQL VIEW, then no need to hard code the COMPANYID column If you include the COMPANYID filed in the Select and Group BY.

 

Here is the modified SQL view for reference:

Create or Alter view ardiscountView as
select CompanyID, ROW_NUMBER() OVER(ORDER BY Usrdiscountcoden ASC) row,   RefNbr,TranType type,Usrdiscountcoden discount,sum(CuryDiscAmt) amount from artran where Usrdiscountcoden is not null
group by CompanyID, RefNbr,TranType,Usrdiscountcoden

 

 

Naveen B
amitr70
Rank
Userlevel 1
Badge

@Naveen Boga  What if I have multiple tables in that view and using joins. How to decide which company id to use?

-- Amit Ranjan
A
Rank

@amitr70 

I had a similar question, and I your question was months ago, but I believe the answer is that we can take the companyID from any table. Unless you have some aggregation that would alter the companyIDs, the website/DAC should automatically filter for the company that you need when the GI makes its request.

Below is the logic of the script where I was troubleshooting the same issue. The placement of ‘CompanyID’ was crucial, as I was getting resultant views that aggregated data from multiple website instances (yikes!). You should be able to pull company ID from any table and get the result you want.

P.S. If someone with better SQL skills than me can achieve this same result without dynamic SQL, please let me know...I think it is necessary since the name and number of columns can be arbitrary, but I would love to be proven wrong here...

 

--Create a view as a placeholder that pivots and retrieves the correct string values

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Step 1: Get distinct AttributeKey values
SELECT @columns = STRING_AGG(QUOTENAME(AttributeKey), ', ')
FROM (SELECT DISTINCT AttributeKey FROM AttributeKeyTable) AS distinct_attributes;

-- Step 2: Create the dynamic SQL statement for the view with explicit conversion
SET @sql = '
CREATE OR ALTER VIEW AKStockItemsByAttributeStringValue AS
SELECT CompanyID, InventoryID, ' + @columns + '
FROM
(
    SELECT
        CompanyID,
        InventoryID, 
        AttributeKey, 
        AttributeValue
    FROM StockItemAttribute
) AS SourceTable
PIVOT
(
    MAX(AttributeValue)
    FOR AttributeKey IN (' + @columns + ')
) AS PivotTable1';

-- Step 3: Execute the dynamic SQL to create or alter the view
EXEC sp_executesql @sql;

GO

--Repeat the same procedure for the numeric columns

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Step 1: Get distinct AttributeKey values
SELECT @columns = STRING_AGG(QUOTENAME(AttributeKey), ', ')
FROM (SELECT DISTINCT AttributeKey FROM ProductAttributeKey) AS distinct_attributes;

-- Step 2: Create the dynamic SQL statement for the view with explicit conversion
SET @sql = '
CREATE OR ALTER VIEW AKStockItemsAttributeNumericValue AS
SELECT CompanyID, InventoryID, ' + @columns + '
FROM
(
    SELECT
        CompanyID,
        InventoryID, 
        AttributeKey, 
        Value
    FROM StockItemAttribute
) AS SourceTable
PIVOT
(
    MAX(Value)
    FOR AttributeKey IN (' + @columns + ')
) AS PivotTable2';

-- Step 3: Execute the dynamic SQL to create or alter the view
EXEC sp_executesql @sql;

GO

-- Finally, join the two views to create the view that has all the correct data in the correct places

DECLARE @string_columns NVARCHAR(MAX);
DECLARE @numeric_columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

--Aggregate column names with aliases for later joining
SELECT @string_columns = STRING_AGG('s.' + QUOTENAME(AttributeKey), ', ')
FROM (SELECT DISTINCT AttributeKey FROM ProductAttributeKeyValidValue) AS distinct_attributes;

SELECT @numeric_columns = STRING_AGG('n.' + QUOTENAME(AttributeKey), ', ')
FROM (SELECT DISTINCT AttributeKey FROM ProductAttributeKey WHERE AttributeKey NOT IN
        (SELECT DISTINCT AttributeKey FROM ProductAttributeKeyValidValue)) AS distinct_attributes;

SET @sql = '
CREATE OR ALTER VIEW AKStockItemsByAttribute AS
SELECT s.CompanyID, s.InventoryID, ' + @string_columns + ', ' + @numeric_columns + '
FROM AKStockItemsAttributeStringValue s
INNER JOIN AKStockItemsAttributeNumericValue n
ON s.InventoryID = n.InventoryID
';

EXEC sp_executesql @sql;

GO

Reply


Terms & ConditionsCookie settings