By using this website, you agree to our Terms of Use (click here)
I've encountered a situation where the quantities shown on the Allocation Details screen differ from what the availability details show when entering that same item on a sales order (and ultimately the quantities that will go onto a shipment) and it seems to occur somewhat frequently across inventory items. Running the "Validate Inventory" process resolves the issue.
Does anyone know exactly what the validate inventory process does in the system, and are there any issues with scheduling it to run daily (overnight) with an automation schedule to validate all items across all warehouses?
I look forward to hearing everyone's experiences. I ran it once to fix bad information in the Inventory Summary screen. It displayed inventory in an "unassigned" location.
I had to run a similar process in AR. "Validate Customer Balances" Customers kept showing up over their credit limit when they weren't even close. I haven't scheduled either one, because I'm trying to understand what creates the issues.
Both these processes make me nervous. Acumatica must know what's going on to make them available.
The numbers in the Inventory Allocation Details screen are calculated on-the-fly in the screen so they should be more accurate.
The numbers in the Inventory Summary screen are coming from the database. If they get messed up, then you have to run Validate Inventory to recalculate them.
As far as I know, the only thing that messes up the availability numbers is when you make changes to the AVAILABILITY CALCULATION section on the Item Classes screen. When you do that, you are basically changing the calculation, but the numbers stored in the database don't get recalculated according to the new formula until you run Validate Inventory.
I'm not sure about Validate Customer Balances. Looking at the help though for that screen, there is a note which says:
We recommend that you validate the customer balances after you change the credit validation rules. Also, you may run this process if there is any discrepancy between the customer balance and the total amount of all the released customer documents. Use the Customer Details (AR.40.20.00) form to view if there is any discrepancy; use the Customer History (AR.65.20.00) report for details on the customer account history.
Did you change the credit validation rules?
I haven't made any changes to how inventory availability is calculated, but that does make sense.
We do have users with access to change credit terms and when an account is placed on hold. Are these the validation rules it's referencing?
That might explain it on the customers side, but I'm not sure about your inventory side.
You might try opening a support case with Acumatica.
Do you have any customizations in place?
None on the inventory side. I need to find another discrepancy and then I'll be in a position to open a case.
Should we move this to Finance?
Two more customers with wacky "unreleased balances" which causes them to be incorrectly placed on credit hold. Had to run the Validate Customer Balances to fix them.
Know for a fact that one customer hasn't had any changes to it's own "credit validation rules". Are their any other "credit validation rules"?
Up to you. Seems fine here in Distribution or in Finance since you're talking about two different screens.
At this point, I think you're probably best off to open a support case with Acumatica and send them a snapshot. If you have the time, maybe you could report back here with what you learn so we could all benefit.
This is kind of related - sorry if I'm not asking in the appropriate place. I have an issue with two invoices that were processed (from shipments) but inventory was not allocated, so now I'm unable to post the invoices and they're just hanging out there. Any ideas on how to fix this?
Thanks!
We have seen similar issues. We had to open the IN issue and release it manually. We also have had to change the bin location of one of the items that wouldn’t release.
What do you mean not allocated - you mean the Sales Order was setup as an SO instead of an SA? Or allocation was forgotten on the Shipment? You can delete the invoice and go back to the shipment. Alternatively you'd need to manually issue out the inventory if you don't want to delete the invoices.
Hi
Did anyone get any reply from Acumatica on the question "Does anyone know exactly what the validate inventory process does in the system"? And why situations occure where this process is needed, for example Available quantity is not correct?
I'm still not sure why it's needed other than recalculating Qty. Available when you change the settings in Availability Calculation Rules (IN201500) screen.
This isn't a comprehensive list, but here are some things that happen:
1. Clicking the PROCESS button on the Validate Inventory (IN505000) screen.
This will update the INSiteStatus.QtyAvail values in the database which are summary values used by the Available column on the Inventory Summary (IN401000) screen. The Inventory Allocation Details (IN402000) screen Available value is calculated on-the-fly rather than using the stored value in the database, but the Inventory Summary (IN401000) screen uses the stored INSiteStatus.QtyAvail values which need to be recalculated every time you change the settings in the Availability Calculation Rules (IN201500) screen.
2. Clicking the PROCESS button on the Validate Inventory (IN505000) screen with Rebuild Item History checked.
This will rebuild the quantities in the INItemSiteHist table based on the transaction history. I'm not sure what would cause these quantities to get messed up.
3. Clicking the PROCESS button on the Validate Inventory (IN505000) screen with Replan Back Orders checked.
This can actually happen automatically every time you RELEASE a new Inventory Receipt if you check the Replan Back-Orders checkbox on the Inventory Preferences (IN101000) screen. Or you run this process. This will look at Sales Order Lines that are currently Backordered and set them to Open again so you can try shipping. I'm not sure though where that Sales Order Line Status gets stored.
The issue that I have seen (and it has happened across multiple clients of mine) is that there is a difference between what the Inventory Summary screen shows, and what the Inventory Allocation Details screen shows. This occurs when there have been no changes made to the availability calculation, and it usually only affects a few items. Running the validation process corrects the issue, but I still have not been able to determine what is causing the actual difference. I also have not seen an answer as whether there is any downside to scheduling the validation process to run on a regular basis.
On the customer side, we have also run across the issue where the customer balance doesn't show correctly and running the validate customer balances must be run to correct it.
Since it has been a recurring issue for some of my customers, we have set automation schedules to run the validation processes for both inventory and customer balances. So far, we have not seen any negative effects from running these on a regular schedule.
As long as it isn't taking tons of time to run, I can't think of a reason why running it on a schedule would be a problem either, but I agree that it would be nice to know what causes the differences to occur.
I have been running this on schedule daily for months ( as well as reindexing for the global which seems to get massed up every time a large operation is done on many records) and have experienced no issues.
I agree that it would be nice to know what this does and why it needs to be run manually.
Does anyone know if the fields from the Inventory Allocation Details Screen is available in the database to pull into a GI?
I'm finding a lot of these discrepancies in our system (I'm hoping they have just been building up slowly from the start). Going to run the validate on all of our items off hours, but I was hoping to setup a GI to monitor discrepancies between INSITE and Inventory Allocation Details.
Thanks everyone!
@hazen-metro
Our first Physical Inventory in Acumatica is coming up soon, so I started looking at this for my own benefit too. Trace doesn't seem to spit out the query used to drive the Inventory Allocation Details screen for me, so I dug through the graph to find how they were putting together their inquiry. It looks like the select is originating from this (with the filter selector values being added by additional ".WhereAnd<>"s. It looks like they are aggregating a bunch of information from INItemPlan?
Here is the raw BQL, you can probably turn this into a GI, but it may end up being a better candidate for a View and unbound DAC:
public virtual PXSelectBase<INItemPlan> GetResultRecordsSelect(InventoryAllocDetEnqFilter filter)
{
PXSelectBase<INItemPlan> cmd =
new PXSelectJoin<INItemPlan,
InnerJoin<INPlanType,
On<INItemPlan.FK.PlanType>,
InnerJoin<Note,
On<Note.noteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<INLocation,
On<INItemPlan.FK.Location>,
LeftJoin<INLotSerialStatus,
On<INItemPlan.FK.LotSerialStatus>,
LeftJoin<BAccount,
On<INItemPlan.FK.BAccount>,
LeftJoin<INSubItem,
On<INItemPlan.FK.SubItem>,
InnerJoin<INSite,
On<INItemPlan.FK.Site>,
LeftJoin<SOOrderShipment,
On<SOOrderShipment.shipmentNoteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<ARRegister,
On<ARRegister.noteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<POOrderReceipt,
On<POOrderReceipt.receiptNoteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<INRegister,
On<INRegister.noteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<SOOrder,
On<SOOrder.noteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<POOrder,
On<POOrder.noteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<POReceipt,
On<POReceipt.noteID, Equal<INItemPlan.refNoteID>>,
LeftJoin<INTransitLine,
On<INTransitLine.noteID, Equal<INItemPlan.refNoteID>>
>>>>>>>>>>>>>>>,
Where<INItemPlan.planQty, NotEqual<decimal0>,
And<INItemPlan.inventoryID, Equal<Current<InventoryAllocDetEnqFilter.inventoryID>>,
And<Match<INSite, Current<AccessInfo.userName>>>>>,
OrderBy<Asc<INSubItem.subItemCD, // sorting must be done with PlanType preceding location
Asc<INSite.siteCD,
Asc<INItemPlan.origPlanType,
Asc<INItemPlan.planType,
Asc<INLocation.locationCD>>>>>>>(this);
if (!SubCDUtils.IsSubCDEmpty(filter.SubItemCD) && PXAccess.FeatureInstalled<FeaturesSet.subItem>())
{
cmd.WhereAnd<Where<INSubItem.subItemCD, Like<Current<InventoryAllocDetEnqFilter.subItemCDWildcard>>>>();
}
if (filter.SiteID != null)
{
cmd.WhereAnd<Where<INItemPlan.siteID, Equal<Current<InventoryAllocDetEnqFilter.siteID>>>>();
}
if (filter.LocationID != null && filter.LocationID != -1) // there are cases when filter.LocationID = -1
{
cmd.WhereAnd<Where<INItemPlan.locationID, Equal<Current<InventoryAllocDetEnqFilter.locationID>>>>();
}
if (!string.IsNullOrEmpty(filter.LotSerialNbr))
{
cmd.WhereAnd<Where<INItemPlan.lotSerialNbr, Like<Current<InventoryAllocDetEnqFilter.lotSerialNbrWildcard>>>>();
}
return cmd;
}
Nice one! Thanks for posting that. At this point, probably not worth going that deep, but if we do continue to have problems, I'm definitely going to come back around to this. Thanks @wyatt-erp!
@wyatt-erp @timrodman Is the reply above blank for others?
Curious to see what his comment about how to handle this in a SQL view?
@jwright Sorry for the delay. The forums stopped displaying that post from @wyatt-erp on the front-end for some reason, but I could still see the content when I went into admin mode. I just stripped out some of the colorful formatting and the code should be displaying now.
If you are just looking for the quantity totals by item and warehouse, without the order level detail, you can get those from the INSiteStatus object.
| Object | Data Field |
| INSiteStatus | Active |
| INSiteStatus | InventoryID |
| INSiteStatus | InventoryID_Description |
| INSiteStatus | LastModifiedDateTime |
| INSiteStatus | QtyActual |
| INSiteStatus | QtyAvail |
| INSiteStatus | QtyExpired |
| INSiteStatus | QtyFixedFSSrvOrd |
| INSiteStatus | QtyFSSrvOrdAllocated |
| INSiteStatus | QtyFSSrvOrdBooked |
| INSiteStatus | QtyFSSrvOrdPrepared |
| INSiteStatus | QtyHardAvail |
| INSiteStatus | QtyINAssemblyDemand |
| INSiteStatus | QtyINAssemblySupply |
| INSiteStatus | QtyINIssues |
| INSiteStatus | QtyINReceipts |
| INSiteStatus | QtyINReplaned |
| INSiteStatus | QtyInTransit |
| INSiteStatus | QtyInTransitToProduction |
| INSiteStatus | QtyInTransitToSO |
| INSiteStatus | QtyNotAvail |
| INSiteStatus | QtyOnHand |
| INSiteStatus | QtyPODropShipOrders |
| INSiteStatus | QtyPODropShipPrepared |
| INSiteStatus | QtyPODropShipReceipts |
| INSiteStatus | QtyPOFixedFSSrvOrd |
| INSiteStatus | QtyPOFixedFSSrvOrdPrepared |
| INSiteStatus | QtyPOFixedFSSrvOrdReceipts |
| INSiteStatus | QtyPOFixedOrders |
| INSiteStatus | QtyPOFixedPrepared |
| INSiteStatus | QtyPOFixedProductionOrders |
| INSiteStatus | QtyPOFixedProductionPrepared |
| INSiteStatus | QtyPOFixedReceipts |
| INSiteStatus | QtyPOOrders |
| INSiteStatus | QtyPOPrepared |
| INSiteStatus | QtyPOReceipts |
| INSiteStatus | QtyProdFixedProdOrders |
| INSiteStatus | QtyProdFixedProdOrdersPrepared |
| INSiteStatus | QtyProdFixedProduction |
| INSiteStatus | QtyProdFixedPurchase |
| INSiteStatus | QtyProdFixedSalesOrders |
| INSiteStatus | QtyProdFixedSalesOrdersPrepared |
| INSiteStatus | QtyProductionAllocated |
| INSiteStatus | QtyProductionDemand |
| INSiteStatus | QtyProductionDemandPrepared |
| INSiteStatus | QtyProductionSupply |
| INSiteStatus | QtyProductionSupplyPrepared |
| INSiteStatus | QtySOBackOrdered |
| INSiteStatus | QtySOBooked |
| INSiteStatus | QtySODropShip |
| INSiteStatus | QtySOFixed |
| INSiteStatus | QtySOFixedProduction |
| INSiteStatus | QtySOPrepared |
| INSiteStatus | QtySOShipped |
| INSiteStatus | QtySOShipping |
| INSiteStatus | SiteID |
| INSiteStatus | SiteID_Description |
| INSiteStatus | SubItemID |
@myrcenary - This would give me the data from inventory summary report (summary report pulls from INSitestatus). But the idea would be to build a GI that compares INSitestatus to Inventory Allocation. In this way you could monitor / catch discrepancies as they appear. I suppose it would be possible to get commitment data from SOLine but then you'd have to compare against HardQTY which again, would come from INSitestatus..
@hazen-metro
Sorry - I misunderstood, and you are correct. The details from @wyatt-erp are the best route, and creating a view from that.
In case anyone else runs across this issue, I did find another solution to my troubles here. I'm not experienced enough to be able to build my own view, so came up with another system.
I created a generic inquiry which has SOLINE data in it to show me the actual # of orders committed. Making sure to add inventory id, qty, and 'qty on shipments.' You want to remove 'qty on shipments' because this means the item is in shipping. You do not want to use order status 'in shipping' because you may have split shipments. Then I created another generic inquiry that pulls the INSITE data. Through combining them in Power BI (or you could do in tableau/excel) I took hard qty + po qty (if you use this in your availability calculations) - total committed to come up with "available." I then ran this against the "available" quantity in the INSITE table. There were some other reasons for discrepancies, but the majority of the time, it is able to show me when I have inventory in INSITE that is incorrect/needs to be validated. Those are the basics of it, there are definitely some small details to get in there so you're making sure to only calculate the right committed numbers. If anyone does need this, let me know, I can share more details.