Will you be sharing the slides? That’s the most popular question asked during pretty much any presentation.
But I decided to skip the slides and use this blog post as my “slides” instead, then I’ll provide you with this URL during the presentation.
I only had 30 minutes during the acuCONNECT session so I focused on the “what” and the “why” during the presentation. The purpose of the rest of this post is to cover the “how” details.
We covered 4 main things during the presentation and you can jump to each of the 4 sections below using these navigation links.
2. SystemEvent SQL View with AugSQL
4. Custom Replenishment Using SQL Stored Procedure
1. SystemEvent SQL View 🔼
Let’s start off by building a simple SQL View, then we’ll connect it to a Generic Inquiry so we can see the data.
But first, a little background on this example…
I had a client who got annoyed when their VAR would publish Customization Projects without telling them. They wanted to see when Customization Projects were getting published.
I had another client who knew that Acumatica was restarting during the day, but they didn’t know exactly when which made it hard to troubleshoot why it was happening. They wanted a way to monitor when Acumatica was restarting.
Both of these events (Customization Project Publishing and Site Restarts) get stored in the SystemEvent table in the Acumatica. But, for some strange reason, the SystemEvent database table isn’t exposed as a Data Access Class (DAC) in Acumatica so you can’t see it and report on it in the Acumatica application. Note: I have a feeling it’s not exposed because it’s not Tenant-specific (there is no CompanyID column).
This is a problem for Acumatica SaaS customers (the majority of Acumatica customers are SaaS) because SaaS customers don’t have access to the Acumatica Database.
So, let’s build a SQL View on the SystemEvent table and connect it to a Data Access Class (DAC), then report on the data within Acumatica.
Since we don’t have access to SQL directly in Acumatica SaaS environments, we need to deploy our SQL View into Acumatica using something called a Customization Project.
Let’s start by creating a Customization Project in the Customization Projects (SM204505) screen and giving it a name. I’m going to call my Customization Project MySQLViews and I’m going to use it to house all of the SQL Views that I create in this environment.
Next, we need to open our Customization Project by clicking on it (MySQLViews), then we can get ready to add some SQL code by clicking on the Database Scripts section on the left-hand side and clicking on the ADD SCRIPT button.
I like to make the Script Name field match the name of the SQL View since I think it looks cleaner so let’s set the Script Name field to MySQLSystemEvent and paste in the following SQL code.
DROP VIEW IF EXISTS dbo.MySQLSystemEvent
GO
CREATE VIEW dbo.MySQLSystemEvent AS
SELECT 2 AS CompanyID,ID,TenantName,[Date],[Level],Source,EventID,ScreenID,[User],Details,Properties
FROM SystemEventTake a look at the CompanyID field in the SQL Code. CompanyID is actually the Tenant (not the Company) in Acumatica. Every SQL View needs to have a CompanyID field. CompanyID won’t be made available for you to report on, but it will be used by the Acumatica Framework to filter the data based on the Acumatica Tenant that you are logged into.
Note in the SQL code that I’ve hardcoded 2 into the CompanyID field. Normally, you would get Company from the data in the table, but SystemEvent doesn’t have a CompanyID column since the System Events are related to the entire Acumatica Instance, not specific Tenants.
Hardcoding the CompanyID field is a secret trick, just make sure that you pick the correct ID of the Tenant that you’re going to be reporting from. Hardcoding the CompanyID can also be done if you want to report on data from other Tenants. Just give the true CompanyID field another name, like Tenant. The fact that you can do this shows that, in my opinion, Acumatica is not truly a multi-tenant solution, even though they market it as such. That’s just fine with me though because I personally think that multi-tenant solutions are overrated since they are too limiting.
After that’s done, we can click the OK button which should return us to the main Customization Project window where we can see that our MySQLSystemEvent script has been added to the Database Scripts section.
IMPORTANT: Publishing the Customization Project should only be done in the evening, when everyone is out of Acumatica.
For the “practical” reasons discussed on Episode #66 (click here) of my Acumatica Podcast, when publishing the Customization Project, I prefer to return to the Customization Projects (SM204505) screen, check the box next to Customization Project, Save, then click the … -> Publish to Multiple Tenants button.
We can check off each Tenant and click the OK button.
After Acumatica is finished validating the Customization Projects (be patient, it could take a few minutes), we’ll get a Validation finished successfully. message and we can click the Publish button.
It might take another few minutes (depending on how many Customization Projects there are) for the Customization Projects to publish. Once they’ve published, we can click the Close Compilation pane button.
When the Customization Projects (SM204505) screen is finished refreshing, we should see a checkmark in the Published column next to our Customization Project, indicating that it has been published.
Start here if using AugSQL. Read further to read about AugSQL.
At this point, the SQL View has been deployed to the database, but the Acumatica Framework doesn’t know about it so we can’t do anything with it yet.
So, we need to add something to our Customization Project and the publish it again.
To tell the Acumatica Framework about our new SQL View, let’s re-open our Customization Project, click the Code section on the left, click the + button on top, set File Template to New DAC, populate the Class Name field with the name of our SQL View (MySQLSystemEvent), check the Generate Members from Database checkbox, and click OK.
Now we should have something that looks like this:
The scary code that we’re looking at is C# code since C# is the language that the Acumatica Framework is written in.
For our purposes though, we only have to pay attention to a few things since the vast majority of the code that we need was generated for us.
The first thing we need to do is tell Acumatica what fields are needed in order to form a primary key on this SQL View. In this case, the primary key is only one field, the Id field.
There’s a section in the code for each field (Id and TenantName in the screenshot below) and we tell Acumatica that Id is the primary key by replacing [PXDBIdentity] with [PXDBIdentity(IsKey = true)] in the Id code section below.
The fields listed in the code correspond to each column in the SQL View, but, for reasons that I don’t understand, Acumatica tries to get fancy and change the names sometimes. For example, even though the column in the SQL View is TenantName, you can see that Acumatica put in a space for the DisplayName in the screenshot above and called it Tenant Name. I’ve seen it do even stranger things like try to shorten words. Maybe artificial intelligence is involved, I don’t know.
Each SQL View column name gets listed 4 or 5 times in each section and I consider it a best practice to follow these 2 simple rules in each section:
1. Make the name in the first 2 or 3 occurrences match the SQL View column name exactly.
2. Make the last 2 occurrences match the SQL View column name exactly, except the first letter needs to be lowercase. Why does the first letter need to be lowercase? I don’t really understand why, but it has something to do with C# and you’ll get an error when publishing the Customization Project if the last 2 occurrences don’t start with a lowercase letter.
You can see in the next screenshot that I followed these 2 rules in the first 2 field sections, with the 4 occurrences of Id and the 5 occurrences of TenantName.
Typically you wouldn’t have to do this, but since we want both the Date and the Time of each event to display, we also need to replace [PXDBDate()] in the Date section with [PXDBCreatedDateTime(InputMask = “g”)] like this.
Before moving on, don’t forget to Save. Here’s a full capture of all of the code that I wound up with.
using System;
using PX.Data;
namespace MySQLViews
{
[Serializable]
[PXCacheName("MySQLSystemEvent")]
public class MySQLSystemEvent : IBqlTable
{
#region Id
[PXDBIdentity(IsKey = true)]
public virtual int? Id { get; set; }
public abstract class id : PX.Data.BQL.BqlInt.Field<id> { }
#endregion
#region TenantName
[PXDBString(128, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "TenantName")]
public virtual string TenantName { get; set; }
public abstract class tenantName : PX.Data.BQL.BqlString.Field<tenantName> { }
#endregion
#region Date
[PXDBCreatedDateTime(InputMask = "g")]
[PXUIField(DisplayName = "Date")]
public virtual DateTime? Date { get; set; }
public abstract class date : PX.Data.BQL.BqlDateTime.Field<date> { }
#endregion
#region Level
[PXDBInt()]
[PXUIField(DisplayName = "Level")]
public virtual int? Level { get; set; }
public abstract class level : PX.Data.BQL.BqlInt.Field<level> { }
#endregion
#region Source
[PXDBString(64, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Source")]
public virtual string Source { get; set; }
public abstract class source : PX.Data.BQL.BqlString.Field<source> { }
#endregion
#region EventID
[PXDBString(1024, InputMask = "")]
[PXUIField(DisplayName = "EventID")]
public virtual string EventID { get; set; }
public abstract class eventID : PX.Data.BQL.BqlString.Field<eventID> { }
#endregion
#region ScreenID
[PXDBString(128, InputMask = "")]
[PXUIField(DisplayName = "ScreenID")]
public virtual string ScreenID { get; set; }
public abstract class screenID : PX.Data.BQL.BqlString.Field<screenID> { }
#endregion
#region User
[PXDBString(64, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "User")]
public virtual string User { get; set; }
public abstract class user : PX.Data.BQL.BqlString.Field<user> { }
#endregion
#region Details
[PXDBString(IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Details")]
public virtual string Details { get; set; }
public abstract class details : PX.Data.BQL.BqlString.Field<details> { }
#endregion
#region Properties
[PXDBString(IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Properties")]
public virtual string Properties { get; set; }
public abstract class properties : PX.Data.BQL.BqlString.Field<properties> { }
#endregion
}
}Now we can publish the Customization Project like we did before by returning to the Customization Projects (SM204505) screen, checking the box next to Customization Project, clicking Save, then clicking the … -> Publish to Multiple Tenants button.
Once we’ve finished publishing the Customization Project (for the 2nd time), we’re now ready to build a Generic Inquiry.
Let’s head over to the Generic Inquiry (SM208000) screen and create a Generic Inquiry that utilizes our new SQL View.
Let’s set Inquiry Title to System Events, check the Make Visible on the UI checkbox, add our new MySQLSystemEvent Data Access Class (DAC) to the TABLES tab, and also add the CRCase DAC. The CRCase DAC is the second part of what we need to do to display both the Date and Time of the event.
Then let’s use the SORT ORDER tab and set the data to sort in Descending order on the Id column by default.
Then let’s go to the RESULTS GRID tab and add the fields. Let’s also use CRCase.CreatedDateTime in the Schema Field column and Date in the Caption column.
Now we can click the VIEW INQUIRY button on top to see our Generic Inquiry which should look something like this.
Our Generic Inquiry now shows us everything that’s in the SystemEvent table. Pretty cool huh? Feel free to browse through to see what kind of events Acumatica stores there.
Now let’s get back to those 2 clients that I mentioned earlier. One client only wanted to see when Customization Projects were getting published and the other client only wanted to see when the Acumatica Site was restarting.
Since it’s kind of cluttered to see all of the events together, let’s create a Dashboard that filters on each event separately to make it easier to digest.
I’m going to add 2 new Widgets to a new Dashboard called System Events (DB000042), but you can add them to an existing Dashboard if you prefer.
To see when Customization Projects are published, let’s Add a new widget, select Data Table, and click the NEXT button.
Then let’s populate the Inquiry Screen field with our System Events (GI640597) Generic Inquiry, click the FILTER SETTINGS button, and filter on EventID Equals Customization_PluginsExecutionCompletedEventId, and click OK.
Next let’s click the COLUMN SETTINGS button, make sure that only Date and User are displayed in the Selected Columns area and click OK.
Now we can set Caption to Customization Project Published and click the FINISH button.
Not a bad looking Dashboard right? Simple and to the point. And it gives the first client what they wanted.
To see when Acumatica is restarting, what the second client wanted, we can copy the CUSTOMIZATION PROJECT PUBLISHED Widget to a new Widget, call it SITE RESTARTED, and filter on the System_StartEventId event.
2. SystemEvent SQL View with AugSQL 🔼
Deploying a SQL View via a Customization Project is kind of annoying for a few reasons:
First, it’s annoying because you have to publish a Customization Project which needs to be done in the evening, after everyone is out of Acumatica. Yuck!
Second, it’s annoying because you don’t get the opportunity to test the SQL View prior to publishing. If there’s a problem with it, troubleshooting the SQL View is pretty much impossible without having a separate development environment with SQL access.
Third, it’s annoying because, even if you have a separate development environment with SQL access, your development environment is going to have an old copy of your production data (hopefully not too old) so you can’t test your SQL View out on your live data. When it comes to reporting, it’s a lot easier when you can develop the report on your live data.
I got so annoyed that I teamed up with an Acumatica developer to solve this problem with…
AugSQL (click here)
AugSQL allows you to run SQL in Acumatica and see the results immediately, without deploying a Customization Project!
This allows us to craft a SQL statement in real-time, while working with our live data.
When I paste our SQL code from earlier into the SQL Command field and click the EXECUTE button in the screenshot below, I can see the results in the grid below.
As you can see in the previous screenshot, the results grid in AugSQL looks just like any grid in Acumatica. You can sort on any column, filter on any column, rearrange columns, and show/hide columns.
Once we have the SELECT statement working the way that we want, we can add the CREATE VIEW statement to it as you can see in the screenshot below.
Clicking the EXECUTE button in the screenshot below will create the SQL View.
We still need the Customization Project for the Data Access Class (DAC) portion of things above, but the DAC is mostly a formality once the SQL code has been established.
Click here to jump to the “Start here if using AugSQL” section above
3. Faster OData Refresh 🔼
I had a client that had built a Generic Inquiry of AR Invoice Lines.
Then they exposed the Generic Inquiry via OData and tried to connect to it from a Power BI Dataflow.
But the Generic Inquiry failed to refresh because it timed out.
That wasn’t super surprising in this case because the Generic Inquiry returned about 940,000 records. It was just too big to refresh over OData.
Note: Solutions like DataSelf ETL+ are great in this situation because they can handle a differential refresh.
But I was curious to see if replacing the regular Data Access Classes with a SQL View could speed things up.
So, I created a SQL View like this:
CREATE VIEW dbo.MySQLARInvoiceLines AS
SELECT T1.CompanyID, T1.TranType+'-'+T1.RefNbr+'-'+CONVERT(VARCHAR(4),T1.LineNbr) 'Key',
T2.RefNbr 'ReferenceNbr', T8.AcctCD 'CustomerID', T5.InventoryCD 'ItemID', T2.DocDate 'Date',
CASE
WHEN T2.DocType='CRM' THEN -T1.BaseQty
ELSE T1.BaseQty
END 'BaseQty',
T5.BaseUnit 'BaseUOM',
( CASE WHEN ( T1.DrCr = 'D') THEN (- 1.0) ELSE 1.0 END * CASE WHEN ( T1.TaxableAmt = .0) THEN ( ( ( ( T1.DiscAmt + CASE WHEN ( T1.TaxableAmt = .0) THEN T1.TranAmt ELSE T1.TaxableAmt END) - T1.DiscAmt) - ( ( T1.TranAmt * ( 1.0 - T1.GroupDiscountRate)) + ( T1.TranAmt * ( 1.0 - T1.OrigGroupDiscountRate)))) - ( ( ( T1.TranAmt - ( ( T1.TranAmt * ( 1.0 - T1.GroupDiscountRate)) + ( T1.TranAmt * ( 1.0 - T1.OrigGroupDiscountRate)))) * ( 1.0 - T1.DocumentDiscountRate)) + ( ( T1.TranAmt - ( ( T1.TranAmt * ( 1.0 - T1.GroupDiscountRate)) + ( T1.TranAmt * ( 1.0 - T1.OrigGroupDiscountRate)))) * ( 1.0 - T1.OrigDocumentDiscountRate)))) ELSE T1.TaxableAmt END) 'NetSales',
CASE WHEN T1.DrCr='D' THEN -1.0 ELSE 1.0 END *
CASE WHEN T1.IsTranCostFinal=CONVERT(BIT,0) THEN T1.TranCostOrig ELSE T1.TranCost END 'Cost'
FROM ARTran T1
LEFT OUTER JOIN ARRegister T2
ON T1.CompanyID=T2.CompanyID
AND T1.TranType=T2.DocType
AND T1.RefNbr=T2.RefNbr
LEFT OUTER JOIN Customer T3
ON T2.CompanyID=T3.CompanyID
AND T2.CustomerID=T3.BAccountID
LEFT OUTER JOIN BAccount T8
ON T2.CompanyID=T8.CompanyID
AND T2.CustomerID=T8.BAccountID
LEFT OUTER JOIN CustomerClass T4
ON T3.CompanyID=T4.CompanyID
AND T3.CustomerClassID=T4.CustomerClassID
LEFT OUTER JOIN InventoryItem T5
ON T1.CompanyID=T5.CompanyID
AND T1.InventoryID=T5.InventoryID
LEFT OUTER JOIN INItemClass T6
ON T5.CompanyID=T6.CompanyID
AND T5.ItemClassID=T6.ItemClassID
LEFT OUTER JOIN InventoryItemCurySettings T7
ON T5.CompanyID=T7.CompanyID
AND T5.InventoryID=T7.InventoryID
AND 'USD'=T7.CuryID
WHERE T2.Released=1 AND T2.Scheduled=0 AND T2.Voided=0 AND T5.InventoryID IS NOT NULL
Then I connected the SQL View to a Generic Inquiry like this:
Now, when I tried refreshing the Power BI Dataflow, it succeeded without failing!
Also, it refreshed substantially faster. It went down from around 13 minutes to around 3 minutes. I even took a screenshot to prove it:
So, using SQL Views makes OData refresh faster, a lot faster.
4. Custom Replenishment Using SQL Stored Procedure 🔼
The last example that I’d like to mention is using a Stored Procedure for a custom replenishment solution. I did this for a client and it’s been working great.
At this point, we’re just populating some custom tables and reporting on them in a Generic Inquiry that has Side Panels for additional information.
The Generic Inquiry also uses a Business Event with a Type of Trigger by Action to allow buyers to check off lines and create a Purchase Order for the recommended quantities by simply clicking a button.
In the future, we’d like to take this a step further and populate the tables behind the Create Purchase Orders (PO505000) screen.
Every company runs Replenishment a little bit differently so it’s nice to be able to customize your replenishment to meet your company’s specific needs.
This particular client is in the food service industry and they supply food to restaurants. They take in orders during the day and ship out at night.
We encapsulated their replenishment logic in a SQL Stored Procedure. This makes it easy to execute.
On the bottom of this post is the code that we used. Since I was constantly running the code to make sure that it worked, after creating the stored procedure I just kept using the ALTER PROCEDURE statement to modify it.
I keep this code in a text file to make it easier to work with in Notepad++, but I can also use the following SQL command in AugSQL to pull the SQL Stored Procedure definition out of the Acumatica Database:
sp_helptext CustomReplenishment_AUG
Running the Stored Procedure is really easy. It can be executed with the EXEC SQL command:
EXEC CustomReplenishment_AUG
With AugSQL, we can schedule the SQL code to run on a schedule so we scheduled the stored procedure to run every morning automatically. It takes a couple of minutes to run and it populates the custom tables with the data that the buyers need to work that day.
Here’s the stored procedure that I’m using for this client. You might want to take this and modify it to meet your own company’s needs:
ALTER PROCEDURE dbo.CustomReplenishment_AUG AS
/* When table structure needs modifying
DROP TABLE CustomReplenishmentOrderHistory_AUG
CREATE TABLE CustomReplenishmentOrderHistory_AUG (
CompanyID INT NOT NULL,
Type CHAR(2) NOT NULL,
OrderType CHAR(3) NOT NULL,
OrderNbr NVARCHAR(15) NOT NULL,
LineNbr INT NOT NULL,
Date datetime2(0),
InventoryID INT,
InventoryCD NVARCHAR(30),
InventoryDesc NVARCHAR(256),
BaseQty DECIMAL(25,6),
BaseUOM NVARCHAR(6),
Qty DECIMAL(25,6),
UOM NVARCHAR(6),
PRIMARY KEY (CompanyID,Type,OrderType,OrderNbr,LineNbr)
)
DROP TABLE CustomReplenishmentDetail_AUG
CREATE TABLE CustomReplenishmentDetail_AUG (
CompanyID INT NOT NULL,
Type CHAR(2) NOT NULL,
OrderType CHAR(3) NOT NULL,
OrderNbr NVARCHAR(15) NOT NULL,
LineNbr INT NOT NULL,
Date datetime2(0),
InventoryID INT,
InventoryCD NVARCHAR(30),
InventoryDesc NVARCHAR(256),
BaseQty DECIMAL(25,6),
BaseUOM NVARCHAR(6),
Qty DECIMAL(25,6),
UOM NVARCHAR(6),
PRIMARY KEY (CompanyID,Type,OrderType,OrderNbr,LineNbr)
)
DROP TABLE CustomReplenishmentHeader_AUG
CREATE TABLE CustomReplenishmentHeader_AUG (
CompanyID INT NOT NULL,
Type CHAR(2) NOT NULL,
InventoryID INT,
InventoryCD NVARCHAR(30),
InventoryDesc NVARCHAR(256),
QtyOnHand DECIMAL(25,6),
QtyAvail DECIMAL(25,6),
BaseQty DECIMAL(25,6),
BaseUOM NVARCHAR(6),
Wk0 DECIMAL(25,6),
Wk1 DECIMAL(25,6),
Wk2 DECIMAL(25,6),
Wk3 DECIMAL(25,6),
Wk4 DECIMAL(25,6),
Wk5 DECIMAL(25,6),
WkAvg DECIMAL(25,6),
DayAvg5 DECIMAL(25,6),
DayAvg7 DECIMAL(25,6),
DaysSupply5 DECIMAL(25,6),
DaysSupply7 DECIMAL(25,6),
WeeksSupply DECIMAL(25,6),
OutDate datetime2(0),
OrderPoint datetime2(0),
Reserve DECIMAL(25,6),
LastReceived datetime2(0),
LastCost DECIMAL(25,6),
LastLanded DECIMAL(25,6),
DeliveredCost DECIMAL(25,6),
VendorID NVARCHAR(30),
VendorName NVARCHAR(255),
VendorLeadTime INT,
BuyerID NVARCHAR(30),
BuyerName NVARCHAR(255)
PRIMARY KEY (CompanyID,Type,InventoryID)
)
*/
TRUNCATE TABLE CustomReplenishmentOrderHistory_AUG
TRUNCATE TABLE CustomReplenishmentDetail_AUG
TRUNCATE TABLE CustomReplenishmentHeader_AUG
DECLARE @MyCurrentTime datetime2(0)
SET @MyCurrentTime = GETDATE()
-- Sales Order History
INSERT INTO CustomReplenishmentOrderHistory_AUG (CompanyID,Type,OrderType,OrderNbr,LineNbr,
Date,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Qty,UOM
)
SELECT L.CompanyID,'SO',L.OrderType,L.OrderNbr,L.LineNbr,
DATEADD(day,1,O.RequestDate),I.InventoryID,I.InventoryCD,I.Descr,
CASE WHEN L.OrderType='CR' THEN 1 ELSE -1 END * L.BaseOrderQty AS 'BaseQty',I.BaseUnit,CASE WHEN L.OrderType='CR' THEN 1 ELSE -1 END * L.OrderQty AS 'Qty',L.UOM
FROM SOLine L
JOIN SOOrder O ON L.CompanyID=O.CompanyID AND L.OrderType=O.OrderType AND L.OrderNbr=O.OrderNbr
JOIN InventoryItem I ON L.CompanyID=I.CompanyID AND L.InventoryID=I.InventoryID
WHERE I.StkItem=1
AND L.OrderType NOT IN ('BG','CR','QT','SM')
-- Detail - AR Invoices
INSERT INTO CustomReplenishmentDetail_AUG (CompanyID,Type,OrderType,OrderNbr,LineNbr,
Date,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Qty,UOM
)
SELECT L.CompanyID,'AR',L.TranType,L.RefNbr,L.LineNbr,
R.DocDate,I.InventoryID,I.InventoryCD,I.Descr,
CASE WHEN L.TranType='CRM' THEN 1 ELSE -1 END * L.BaseQty AS 'BaseQty',I.BaseUnit,
CASE WHEN L.TranType='CRM' THEN 1 ELSE -1 END * L.Qty AS 'Qty',L.UOM
FROM ARTran L
JOIN ARInvoice H ON L.CompanyID=H.CompanyID AND L.TranType=H.DocType AND L.RefNbr=H.RefNbr
JOIN ARRegister R ON L.CompanyID=R.CompanyID AND L.TranType=R.DocType AND L.RefNbr=R.RefNbr
JOIN InventoryItem I ON L.CompanyID=I.CompanyID AND L.InventoryID=I.InventoryID
WHERE I.StkItem=1
AND L.TranType IN ('INV','DRM','CRM')
AND DATEPART(week,R.DocDate)>=DATEPART(week,DATEADD(week,-5,@MyCurrentTime))
-- Detail - Sales Orders
INSERT INTO CustomReplenishmentDetail_AUG (CompanyID,Type,OrderType,OrderNbr,LineNbr,
Date,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Qty,UOM
)
SELECT CompanyID,Type,OrderType,OrderNbr,LineNbr,
Date,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Qty,UOM
FROM CustomReplenishmentOrderHistory_AUG
WHERE DATEPART(week,Date)>=DATEPART(week,DATEADD(week,-5,@MyCurrentTime))
-- Detail - Purchase Orders
INSERT INTO CustomReplenishmentDetail_AUG (CompanyID,Type,OrderType,OrderNbr,LineNbr,
Date,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Qty,UOM
)
SELECT L.CompanyID,'PO',L.OrderType,L.OrderNbr,L.LineNbr,
L.PromisedDate,I.InventoryID,I.InventoryCD,I.Descr,
L.BaseOpenQty,I.BaseUnit,L.OpenQty,L.UOM
FROM POLine L
JOIN POOrder O ON L.CompanyID=O.CompanyID AND L.OrderType=O.OrderType AND L.OrderNbr=O.OrderNbr
JOIN InventoryItem I ON L.CompanyID=I.CompanyID AND L.InventoryID=I.InventoryID
WHERE I.StkItem=1
AND L.OrderType IN ('RO')
AND O.Status='N'
AND L.Completed=0
AND L.BaseOpenQty>0
-- Detail - Quantity On Hand
INSERT INTO CustomReplenishmentDetail_AUG (CompanyID,Type,OrderType,OrderNbr,LineNbr,
Date,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Qty,UOM
)
SELECT Q.CompanyID,'IN','IN' AS 'OrderType',I.InventoryCD,1 AS 'LineNbr',
@MyCurrentTime AS 'Date',I.InventoryID,I.InventoryCD,I.Descr,
Q.QtyOnHand,I.BaseUnit,Q.QtyOnHand,I.BaseUnit
FROM INSiteStatus Q
JOIN InventoryItem I ON Q.CompanyID=I.CompanyID AND Q.InventoryID=I.InventoryID
WHERE Q.QtyOnHand<>0
-- Header - AR Invoices
INSERT INTO CustomReplenishmentHeader_AUG (CompanyID,Type,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Wk0,Wk1,Wk2,Wk3,Wk4,Wk5
)
SELECT D.CompanyID,D.Type AS 'Type',D.InventoryID,
MAX(D.InventoryCD) AS 'InventoryCD',MAX(D.InventoryDesc) AS 'InventoryDesc',
SUM(D.BaseQty) AS 'BaseQty',MAX(I.BaseUnit) AS 'BaseUnit',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,0,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk0',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-1,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk1',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-2,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk2',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-3,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk3',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-4,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk4',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-5,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk5'
FROM CustomReplenishmentDetail_AUG D
JOIN InventoryItem I ON D.CompanyID=I.CompanyID AND D.InventoryID=I.InventoryID
WHERE D.Type='AR'
GROUP BY D.CompanyID,D.Type,D.InventoryID
-- Header - Sales Orders
INSERT INTO CustomReplenishmentHeader_AUG (CompanyID,Type,InventoryID,InventoryCD,InventoryDesc,
BaseQty,BaseUOM,Wk0,Wk1,Wk2,Wk3,Wk4,Wk5,WkAvg
)
SELECT D.CompanyID,D.Type AS 'Type',D.InventoryID,
MAX(D.InventoryCD) AS 'InventoryCD',MAX(D.InventoryDesc) AS 'InventoryDesc',
SUM(D.BaseQty) AS 'BaseQty',MAX(I.BaseUnit) AS 'BaseUnit',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,0,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk0',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-1,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk1',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-2,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk2',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-3,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk3',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-4,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk4',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-5,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END) AS 'Wk5',
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-1,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END)*.5 +
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-2,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END)*.3 +
SUM(CASE WHEN DATEPART(week,D.Date)=DATEPART(week,DATEADD(week,-3,@MyCurrentTime)) THEN D.BaseQty ELSE 0 END)*.2 AS
'WkAvg'
FROM CustomReplenishmentDetail_AUG D
JOIN InventoryItem I ON D.CompanyID=I.CompanyID AND D.InventoryID=I.InventoryID
WHERE D.Type='SO'
GROUP BY D.CompanyID,D.Type,D.InventoryID
-- Header - Calculated Fields
UPDATE H SET QtyOnHand = ISNULL(D.BaseQty,0)
FROM CustomReplenishmentHeader_AUG AS H
LEFT OUTER JOIN CustomReplenishmentDetail_AUG AS D
ON H.CompanyID=D.CompanyID
AND D.Type='IN'
AND H.InventoryID=D.InventoryID
UPDATE H SET QtyAvail = ISNULL(INSiteStatus.QtyAvail,0)
FROM CustomReplenishmentHeader_AUG AS H
LEFT OUTER JOIN INSiteStatus
ON H.CompanyID=INSiteStatus.CompanyID
AND INSiteStatus.SiteID=10001
AND H.InventoryID=INSiteStatus.InventoryID
UPDATE CustomReplenishmentHeader_AUG
SET WkAvg = Wk1*.5 + Wk2*.3 + Wk3*.2
UPDATE CustomReplenishmentHeader_AUG
SET DayAvg5 = WkAvg/5
UPDATE CustomReplenishmentHeader_AUG
SET DayAvg7 = WkAvg/7
UPDATE CustomReplenishmentHeader_AUG
SET DaysSupply5 = CASE WHEN ISNULL(WkAvg,0)=0 THEN 0 ELSE QtyAvail/DayAvg5*-1 END
UPDATE CustomReplenishmentHeader_AUG
SET DaysSupply7 = CASE WHEN ISNULL(WkAvg,0)=0 THEN 0 ELSE QtyAvail/DayAvg7*-1 END
UPDATE CustomReplenishmentHeader_AUG
SET OutDate =
CASE
WHEN DATEPART(WEEKDAY,DATEADD(DAY,CASE WHEN DaysSupply7>0 THEN DaysSupply7 ELSE 0 END,GETDATE()))=7
THEN DATEADD(DAY,2,DATEADD(DAY,CASE WHEN DaysSupply7>0 THEN DaysSupply7 ELSE 0 END,GETDATE()))
WHEN DATEPART(WEEKDAY,DATEADD(DAY,CASE WHEN DaysSupply7>0 THEN DaysSupply7 ELSE 0 END,GETDATE()))=1
THEN DATEADD(DAY,1,DATEADD(DAY,CASE WHEN DaysSupply7>0 THEN DaysSupply7 ELSE 0 END,GETDATE()))
ELSE DATEADD(DAY,CASE WHEN DaysSupply7>0 THEN DaysSupply7 ELSE 0 END,GETDATE())
END
UPDATE H SET Reserve = -INSiteStatus.QtySOBooked - INSiteStatus.QtySOShipped - INSiteStatus.QtySOShipping
FROM CustomReplenishmentHeader_AUG AS H
LEFT OUTER JOIN INSiteStatus
ON H.CompanyID=INSiteStatus.CompanyID
AND INSiteStatus.SiteID=10001
AND H.InventoryID=INSiteStatus.InventoryID
UPDATE CustomReplenishmentHeader_AUG SET LastReceived =
(
SELECT TOP 1 TranDate
FROM INTran
WHERE CompanyID=CustomReplenishmentHeader_AUG.CompanyID
AND InventoryID=CustomReplenishmentHeader_AUG.InventoryID
AND TranType='RCP' AND Released=1
ORDER BY CreatedDateTime DESC
)
UPDATE H SET LastCost = I.UsrNVFSLastCost, LastLanded = I.UsrNVFSLastLandedCost,
DeliveredCost = I.UsrNVFSLastCost + I.UsrNVFSLastLandedCost
FROM CustomReplenishmentHeader_AUG AS H
JOIN InventoryItem I ON H.CompanyID=I.CompanyID AND H.InventoryID=I.InventoryID
UPDATE H SET VendorID = VB.AcctCD, VendorName = VB.AcctName, VendorLeadTime = TRY_CAST(ISNULL(VLT.Value,0) AS INT),
BuyerID = VBuyer.AcctCD, BuyerName = VBuyer.AcctName
FROM CustomReplenishmentHeader_AUG AS H
JOIN InventoryItem I
ON H.CompanyID=I.CompanyID
AND H.InventoryID=I.InventoryID
LEFT OUTER JOIN InventoryItemCurySettings IC
ON H.CompanyID=IC.CompanyID
AND H.InventoryID=IC.InventoryID
AND 'USD'=IC.CuryID
LEFT OUTER JOIN Vendor V
ON IC.CompanyID=V.CompanyID
AND IC.PreferredVendorID=V.BAccountID
LEFT OUTER JOIN BAccount VB
ON IC.CompanyID=VB.CompanyID
AND IC.PreferredVendorID=VB.BAccountID
LEFT OUTER JOIN CSAnswers VLT
ON VB.CompanyID=VLT.CompanyID
AND VB.NoteID=VLT.RefNoteID
AND VLT.AttributeID='Vend04'
LEFT OUTER JOIN BAccount VBuyer
ON V.CompanyID=VBuyer.CompanyID
AND V.UsrNVFSBuyer=VBuyer.BAccountID
UPDATE CustomReplenishmentHeader_AUG
SET WeeksSupply = CASE WHEN DaysSupply5=0 THEN 0 ELSE DaysSupply5 / 5 END
UPDATE CustomReplenishmentHeader_AUG
SET OrderPoint =
CASE
WHEN DATEPART(WEEKDAY,DATEADD(DAY,-VendorLeadTime,OutDate))=7
THEN DATEADD(DAY,-1,DATEADD(DAY,-VendorLeadTime,OutDate))
WHEN DATEPART(WEEKDAY,DATEADD(DAY,-VendorLeadTime,OutDate))=1
THEN DATEADD(DAY,-2,DATEADD(DAY,-VendorLeadTime,OutDate))
ELSE DATEADD(DAY,-VendorLeadTime,OutDate)
END