Will you be sharing the slides? Yes! This blog post is “the slides” 😀
And here is the recording from the session. I liked that they only gave us 30 minutes this year because it forced me to distill things down, even though I did go over by a minute 😁
Originally, I had planned to show multiple SQL View examples, but, as I was building out the examples, I realized that I was having trouble coming up with compelling examples. There are a lot less reasons to use SQL Views on live Acumatica data these days. There are still reasons to do it, but not as many as there used to be. Why? Because of a new feature in Acumatica 2024 R1 that allows you to use a Generic Inquiry in another Generic Inquiry, which essentially opens the door to subqueries in Generic Inquiries.
It’s still not a bad idea to use SQL Views for some situations, like when you need a UNION ALL statement to “append” data, but, even then, there’s a (somewhat clunky) workaround that allows you to pull that off using native Generic Inquiry functionality, without using a SQL View.
So, the night before this presentation, I switched gears and decided to go with an example that I felt was a more compelling example for SQL Views and SQL in general.
The technique that I highlight in this session is to pull data from Acumatica and store it in custom tables, then make that data available to Acumatica using SQL Views and Custom Data Access Classes (DACs).
This technique is useful when you need to perform more than just a few simple calculations on your data because performing too many calculations on live data can make your Generic Inquiries, Reports, and Dashboards in Acumatica run really slow.
Storing the results of calculations in custom tables allows you to do the “heavy lifting” on a schedule (weekly, daily, hourly, etc.). Since the “heavy lifting” now doesn’t need to be done every time you run the report, your Generic Inquiries, Reports, and Dashboards will run much faster when using this technique.
Everyone wants live data, but live data comes at a cost. The truth is though, that you don’t need live data when doing analysis. For example, analyzing sales trends from the last 6 months, summarizing data from Manufacturing Bills of Material, calculating predictive analytics using things like CRM Activities, etc.
Or, as I show in this session, building your own custom replenishment solution that’s tailored to your unique business requirements. The out-of-the-box Replenishment Module in Acumatica is fine for many companies, but some companies need to make modifications to the calculations in Replenishment.
If you want to modify Replenishment, you could hire an Acumatica Developer to modify the code behind the Acumatica Replenishment Module. Or, you can build your own Replenishment Module using the SQL technique showed in this session.
Why use SQL for Replenishment modifications? Here are 4 reasons that I can think of:
1. It’s less likely to break during upgrades
2. It costs less
3. It takes less time
4. There are a lot more people who have the skills to do the work
Regarding that last point, as Acumatica continues to grow, it can be difficult to find qualified Acumatica Developers.
There are A LOT more SQL Developers out there than there are Acumatica Developers. Here’s chart that I put together with my estimates on the number of Acumatica Developers vs. SQL Developers, vs. Microsoft Excel Developers:
That’s a screenshot from a webinar that I did on AugSQL where I go into depth about why I think SQL is just as relevant today as it was in the 1970s, especially when it comes to Acumatica Cloud ERP.
You can find that webinar here:
AugForums.com/AugSQL
AugSQL is a product that I developed together with an Acumatica Developer. AugSQL allows you to work with SQL inside of Acumatica using your web browser, even within Acumatica SaaS environments.
You don’t need AugSQL for most of what I show in this session since you can easily publish SQL using a Customization Project in Acumatica. Acumatica has always been very SQL friendly. In fact, “SQL-friendly” was a reason why a lot of Acumatica VARs added Acumatica to their ERP portfolios. I know that because a lot of VARs reached out to me back in the 2013 to 2016 time frame, when Acumatica was still relatively unknown and didn’t have that many partners. I talked to a lot of those VARs and being SQL-friendly was a common reason why they were interested in Acumatica over NetSuite.
However, you do need AugSQL if you want to schedule SQL code to run on a schedule like I do with the EXEC CustomReplenishment_AUG SQL statement in this session.
Next is a graphic showing the overall flow of what I show in this session. I read this graphic from the bottom up because, even though it illustrates that the Generic Inquiry is “sitting on top” of the Data Access Class (DAC), which is “sitting on top” of the SQL View, which is “sitting on top” of the SQL Table, which is populated by the SQL Stored Procedure, the process starts at the bottom and “flows” to the top.
After the graphic, you’ll find all of the SQL Code (1 SQL Stored Procedure and 2 SQL Views) that I showed during this session.
Please do add comments to the bottom of this page so I can know what you think. And don’t be afraid to be honest by commenting on the good, the bad, and the ugly. You won’t hurt my feelings because I’m always looking for ways to improve.
Overall Flow
For you discerning SQL people out there, you will notice that the Stored Procedure code needs some help to work in the real world, especially when it comes to collecting the Accounts Receivable Invoice data. That’s because I threw it together the night before the presentation. I definitely want to circle back and update it with something that is more real-world appropriate.
In the meantime, please do leave a comment on the bottom of this page with suggestions about how to improve the following SQL code.
-- Stored Procedure
-- CustomReplenishment_AUG
CREATE OR ALTER PROCEDURE dbo.CustomReplenishment_AUG AS
-- Let's capture the current Date/Time
-- We'll use this later
DECLARE @MyCurrentTime datetime2(0)
SET @MyCurrentTime = GETDATE()
-- We are going to create 2 new custom tables in this Stored Procedure
-- So let's drop them if they already exist
DROP TABLE IF EXISTS CustomReplenishmentDetail_AUG
DROP TABLE IF EXISTS CustomReplenishmentHeader_AUG
-- Create our Detail custom table for this Stored Procedure
CREATE TABLE CustomReplenishmentDetail_AUG (
CompanyID INT NOT NULL,
Type CHAR(2) NOT NULL,
TranType CHAR(3) NOT NULL,
RefNbr NVARCHAR(15) NOT NULL,
LineNbr INT NOT NULL,
Date datetime2(0),
InventoryID INT,
Qty DECIMAL(25,6)
PRIMARY KEY (CompanyID, Type, TranType, RefNbr, LineNbr)
)
-- Create our Header custom table for this Stored Procedure
CREATE TABLE CustomReplenishmentHeader_AUG (
CompanyID INT NOT NULL,
InventoryID INT,
PurchaseOrders DECIMAL(25,6),
QuantityOnHand DECIMAL(25,6),
ARInvoices DECIMAL(25,6),
TotalReplenishment DECIMAL(25,6)
PRIMARY KEY (CompanyID, InventoryID)
)
-- Detail Lines - Purchase Orders
INSERT INTO CustomReplenishmentDetail_AUG
(
CompanyID, Type, TranType, RefNbr, LineNbr,
Date, InventoryID, Qty
)
SELECT L.CompanyID, 'PO', L.OrderType, L.OrderNbr, L.LineNbr,
L.PromisedDate, L.InventoryID, L.BaseOpenQty
FROM POLine L
LEFT JOIN POOrder O
ON L.CompanyID=O.CompanyID
AND L.OrderType=O.OrderType
AND L.OrderNbr=O.OrderNbr
WHERE
L.InventoryID IS NOT NULL
AND L.OrderType IN ('RO')
AND O.Status='N'
AND L.Completed=0
AND L.BaseOpenQty>0
-- Detail Lines - Quantity On Hand
INSERT INTO CustomReplenishmentDetail_AUG
(
CompanyID, Type, TranType, RefNbr, LineNbr,
Date, InventoryID, Qty
)
SELECT CompanyID, 'IN', SiteID, InventoryID, 1,
@MyCurrentTime, InventoryID, QtyOnHand
FROM INItemStats Q
WHERE
QtyOnHand<>0
-- Detail Lines - Accounts Receivable Invoices
INSERT INTO CustomReplenishmentDetail_AUG
(
CompanyID, Type, TranType, RefNbr, LineNbr,
Date, InventoryID, Qty
)
SELECT CompanyID, 'AR', TranType, RefNbr, LineNbr,
TranDate, InventoryID,
CASE WHEN TranType='CRM' THEN 1 ELSE -1 END * Qty
FROM ARTran
WHERE
InventoryID IS NOT NULL
AND DATEPART(week,TranDate)>=DATEPART(week,DATEADD(week,-5,@MyCurrentTime))
-- We're done getting the detailed data from Acumatica
-- Now let's summarize the data that we already collected
-- Summarize Everything (1 row per Item) into our Header table
INSERT INTO CustomReplenishmentHeader_AUG
(
CompanyID, InventoryID,
PurchaseOrders, QuantityOnHand, ARInvoices
)
SELECT CompanyID, InventoryID,
SUM(CASE WHEN Type='PO' THEN Qty ELSE 0 END),
SUM(CASE WHEN Type='IN' THEN Qty ELSE 0 END),
SUM(CASE WHEN Type='AR' THEN Qty ELSE 0 END)
FROM CustomReplenishmentDetail_AUG
GROUP BY CompanyID, InventoryID
UPDATE CustomReplenishmentHeader_AUG
SET TotalReplenishment = PurchaseOrders + QuantityOnHand + ARInvoicesCREATE OR ALTER VIEW CustomReplenishmentViewDetail_AUG AS
SELECT CompanyID, Type, TranType, RefNbr, LineNbr, Date, InventoryID, Qty
FROM CustomReplenishmentDetail_AUGCREATE OR ALTER VIEW CustomReplenishmentViewHeader_AUG AS
SELECT CompanyID, InventoryID, PurchaseOrders, QuantityOnHand, ARInvoices, TotalReplenishment
FROM CustomReplenishmentHeader_AUG