I have a bit of a problem regarding dead stock I am trying to add the Location field to the dead stock screen
This is what I currently have:
Non filtered by locationFiltered by location
Now the problem I am facing is with the dead stock calculations Acumatica stores inventory history in INItemSiteHistByCostCenterD, but there is no table that has it based on location, or includes the locationid field (like INItemSiteStatusByCostCenter vs INLocationStatusByCostCenter)
Would there be a nice work around, or would I have to build a custom view/DAC To be honest, I just want more advice ;)
Best answer by Kovatus
Good Morning to All
Thanks @Nilkanth Dipak for your suggestion, I have taken it to heart
Here is what I have done:
I built the SQL View and added it as a database script (Create View script)
After publishing, and having the view created, I created a DAC with the same name as the view, and selected the “Generate Members from database” (something in this line)
I modified the existing DeadStock Screen
I added the location filter to the header
I added the Location Description and Id to the grid
I then overrode the DAC for the DeadStock grid to include location
Having the view, made calculations simple
If anyone ever needs to do this, I attached the customization package
Take note of the tables used in the view if some of the logic in the code does not make sense
Build a Custom DAC and View with INItemSiteHistByCostCenterD and INLocationStatus Since Acumatica doesn’t natively track inventory history by location, a custom DAC and view could aggregate data from INItemSiteHistByCostCenterD and INLocationStatus to approximate dead stock calculations by location.
Create a Generic Inquiry (GI) with Calculations for Dead Stock by Location If your needs are primarily analytical and don’t require a fully custom DAC, consider creating a Generic Inquiry (GI) that includes the LocationID field and performs dead stock calculations.
Create a GI that Joins INLocationStatus and INItemSiteHistByCostCenterD: Pull in LocationID, InventoryID, and SiteID from INLocationStatus. Join to INItemSiteHistByCostCenterD to pull in historical data at the site level.
Define a SQL View in the SQL Database: Join INLocationStatus and INItemSiteHistByCostCenterD based on SiteID and InventoryID. Calculate dead stock based on movement dates. Create a DAC for the SQL View: Use the SQL view as a data source for a custom DAC in Acumatica to expose it in the UI or reports. This approach can be helpful if you need to perform complex aggregations or transformations that are difficult to achieve in BQL.
Thanks @Nilkanth Dipak for your suggestion, I have taken it to heart
Here is what I have done:
I built the SQL View and added it as a database script (Create View script)
After publishing, and having the view created, I created a DAC with the same name as the view, and selected the “Generate Members from database” (something in this line)
I modified the existing DeadStock Screen
I added the location filter to the header
I added the Location Description and Id to the grid
I then overrode the DAC for the DeadStock grid to include location
Having the view, made calculations simple
If anyone ever needs to do this, I attached the customization package
Take note of the tables used in the view if some of the logic in the code does not make sense
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
