GI with Sales & Inventory
I am working on a GI to create a calculation for inventory buffer levels based on average sales and current inventory. However, I am running into a problem trying to add inventory per item, where for some reason it completely changes the sales information.
Here are the tables I am using (before trying to add inventory information):
The relations of these tables are set up in the following way:
SOOrder > (Left) > BAccount (Parent CustomerID, child BAccountID)
SOOrder > (Left) > SOLine (Parent orderNbr, child orderNbr)
SOOrder > (Left) > Customer (Parent customerID, child bAccountID)
SOLine > (Left) > InventoryItem (Parent InventoryID, child InventoryID)
The only parameters are for start and end date based on SOOrder.OrderDate.
The conditions are the following:
Dates for parameters,
InventoryItem.ItemClassID Does Not Equal (four different values, one per line),
Customer.CustomerClassID Equals one value).
It is grouped and sorted by InventoryItem.InventoryCD
The Results Grid is the InventoryCD from InventoryItem, the OrderQty from SOLine, and a calculation dividing the sum of the order qty per sku by 120.
This is what I get with all of this information:
These are the correct sales figures for a 120 day period. Now, when I add the table [InLocationStatus], I relate it with the parent table of InventoryItem, with InventoryID for both tables as the parent and child, and this is what happens to my sales figures:
As you see, the exact same items become much larger numbers, and I'm not certain why. I've tried using the Aggregate Function with Count/Max/Sum, etc., but none of them have the same figures as the original without adding inventory information.
P.S. The reason I'm using [InLocationStatus] is because I need Qty Available, not Qty On Hand, etc. I need to know the exact quantity that's available for shipping, etc.
Thank you in advance.
@Brandon, if you have 5 of one item in location one and 10 of the same item in location 2, you will bring in a total count of 15. However, since there are two location records, my total sales for the item gets doubled. This, in turn, affects the other calculations.
I suspect you are going to have challenges putting everything you want into a single GI. If you were doing this in SQL, you would calculate your total on-hand/available quantities from the INLocationStatus table is a subquery and then use the results in your GI. However, this isn't possible in Acumatica. I suspect you will have to get your results possibly from a report with subreports or outside of Acumatica using data from multiple GIs.
I remember having an issue like this before where my totals were doubling, tripling or whatever due to a join of another table. I solved it by dividing the field i was aggregating by a constant value from the join table. So lets say my aggregate column was 5 times more than it should be, i divided the field by a constant in the join table which also had the value 5. Then when the amount was aggregated, the total was correct.
I know this is a bit of a hack and it very much depends on whether you can find a suitable field to divide your amount fields by - basically you are looking for a value which matches the total number of rows you are exploding your query by.
Ultimately I didn't end up using that Query as I found a totally different solution to my particular problem, but just thought I would throw it out there in case it helped.