Best RULES to use when tracking inventory qty

rbross

Hello,

Just wanted to see how others are managing inventory location qty or any type of Qty with Rules in Aware?
BO1= InventoryMaster
BO2= InvLocation
Relationships= InventoryMaster Owns InvLocation Relationship added= O_InvLocation
InvLocation Added OB_InventoryMaster

Option 1. Rules Added to InventoryMaster OnShelfQty
Rule #1 If InvLocation WAS ADDED TO InventoryMaster.O_InvLocation Then
INCREASE InventoryMaster.IM_Qty3OnShelf BY AddedInvLocation.IL_Qty

Rule #2 If InvLocation WAS REMOVED FROM InventoryMaster.O_InvLocation Then
REDUCE InventoryMaster.IM_Qty3OnShelf BY RemovedInvLocation.IL_Qty

Rule #3 If InvLocation FROM InventoryMaster.O_InvLocation WAS CHANGED Then
INCREASE InventoryMaster.IM_Qty3OnShelf BY ChangedInvLocation.IL_Qty-OLD_VALUE(ChangedInvLocation.IL_Qty)

Option #2 Rule added to InventoryMaster to manage OnShelfQty
Rule #1 If InvLocation FROM InventoryMaster.O_InvLocation WAS CHANGED Then
InventoryMaster.IM_Qty3OnShelf=SUM InvLocation.IL_Qty WHERE (InvLocation IN InventoryMaster.O_InvLocation)

Option #1 is working, but just wondering if Option #2 is a better rule to use? The only thing I can think of is that it may slow it down as it needs to do a SUM of all the child records, where Rules for Option #1 are just adding or subtracting for one (1) record. There won't be that many records

Which Rule(s) are better optimized for performance and managing this?

I have several BO's I need to add these types of rules to and I want to keep it as simple as possible and optimized.
I have the same rules for Option #1 setup in InventoryMaster for when an InventoryMaster item is added, removed or updated in a package. The rule will update PackageQty.
There will be similar rules for when a package is added to a shipment as well, OnHandQty will be updated. OnHandQty does not change until the item or package the item is in gets added to a shipment.

Let the critiquing begin!

ACDC

So you are talking about accounting functions here, I would handle this very differently.

To simplify the response I am not going to use your object names, but rather explain in a broader sense.

Example:

Product ( peer relation with ProductLineItem)
ProductLineItem owned by (Invoice Object, Purchase Object )

So the Product object now has 2 peer related Line Item Objects that relate to the movement of items in and out
Each one of these references is totalled by a rule on the product object that uses the COUNT function to total the Qty column on each reference

The total of the InvoiceLineItems = TotalQtySold
The total of the ReceiptLineItems= TotalQtyReceived
The difference between the two above totals = Stock on Hand.

So as you process the invoices and receipts they are automatically being totalled and guaranteed to always be correct. For example, if you delete an invoice lineItem, the balance updates automatically by way of the COUNT function firing upon the deletion .

Once you have this working, then introduce the StoreObject (InventoryLocation) into the mix and ensure you have each Store referenced on the Product. So when you Invoice you also assign the Invoice to a Store and likewise a Receipt to a store. So the Store object has an In and an Out references that are being totalled in the same way as the Product totals.

Hope I am making sense, had a long day, I am sure you can work it out from here

rbross

Thanks for the response.
In this case, I think SUM needs to be used and not COUNT as we need the total Qty for an inventory item from each of the locations it is stored in. COUNT will just give us the number of invlocation items, not the qty. An item can be stored in several locations and each location will have a qty. Location 1 could have a qty of 10 location 2 could have a qty of 5 giving a total qty of 15 available pieces for that item. COUNT would just return 2 for two locations for the item.

The relationship is an Owned By because you can't have a location for the item if it does not exist.
The question is can I get away with using just one Rule or keep the rules separate for Added, Removed and Changed?

ACDC

Yes you are right. I meant to say SUM ( long day at the office )

Also I meant to say the LineItems in each case need to contain the StoreLocation reference, so each time a line item is created it takes on the StoreLocation from the IN or Out transaction

Using the SUM Rule should suffice as it keeps it simple

BLOMASKY

Let me offer a slightly different view.
I understand the view of taking all the sales and the receipts and the net diff. "SHOULD" be the current balance, however I have some problems with that.
1) If you ever want to purge off old data . i.e. sales more than "X" years old, this breaks
2) This does not handle any "adjustments'" i.e. pilferage, damage, lost, adjustments made after a physical inventory is taken, etc. etc.

I view all inventory system with the idea that there is one table that is "GOOD" and recompute the others from that. I have something similar in my application. We have lumber inventory, and each item has multiple "Units" (a unit is a bundle of wood") where each one can have a diff. quantity. So since we can see, count the Units, they are the "GOOD" qty. Anytime we need to "fix" our inventory balances, we call a stored procedure that does the following

Update Inventory set QtyOnHand = 0
Update Inventory set QtyOnHand = TotalUnitQty
from Inventory I inner join (select ob_Inventory_RID, sum(UnitQty) from Units where UnitQty > 0 group by ob_Inventory_RID) as Z on I.ID = Z.ob_Inventory_RID

Yes, I assume that my code will always keep the totals correct in the inventory bo, but since this SP takes about 3 seconds to run (with about 100,000 units) we do run it every night.

I do use your Rule 1 and Rule 2 to keep the Inventory table updated for whenever changes are made to units.

Bruce

ACDC

1) If you ever want to purge off old data. i.e. sales more than "X" years old, this breaks

From an accounting perspective we never want to purge records, if we need to do this from a techie perspective to keep the system efficient then there should be some kind of attribute that caters for the purge event where a purge process records open and closing balances

2) This does not handle any "adjustments'" i.e. pilferage, damage, lost, adjustments made after a physical inventory is taken, etc. etc.

Changes to inventory related to the above should be carried out by an adjustment entry, call it a "Journal" entry. So added to the Invoice and Receipt reference there should be a Journal reference handling such abnormal events to update new balances

Once all the right references are in place, using a SUM function is all one needs, this way you are always guaranteed of being in balance and it's a simple approach....best of all it's real-time

....probably overkill for the application, but in my mind anything related to this kind of thing, accounting principles should be used in the design

rbross

Thanks for the replies everyone.

Let me explain this process in a little more detail. They have the ability to pull inventory from more than one location. Not every item will have Qty in a location.
This is a non-profit business and they do not follow the standard inventory management rules where ever item has a location in the warehouse or inventory store.
Sometimes they are packing items as soon as they come in the door and then update the computer later. They can pull inventory qty from 3 area:
Loose Qty (not in a location)
Location Qty ( Qty that has a location)
Expiration Date Qty ( items like medical items vitamins etc. that have exp. dates, each exp. date has its own qty)
Locations and Exp. Dates are child records of the inventory item.

QtyOnHand does not get updated unless they ship a packaged or a loose item.

I have this working where they just pull from a location but this will not work for them as they do not have the time to put every item in a location, some items are just packed loose.

The process I have now that works for location and is called when they click the Create or Save button in the form is:

Passing PackageDetail as a parmaeter
FIND InvLocation WHERE InvLocation.PM_PackageDetail.ID=PackageDetail.ID TAKE BEST 1
FIND InventoryMaster WHERE InventoryMaster.PM_PackageDtl.ID=PackageDetail.ID TAKE BEST 1

IF PackageDetail.PD_Qty>ThisInvLocation.IL_Qty+OLD_VALUE(PackageDetail.PD_Qty)
THEN
REPORT ERROR'Invalid Qty'
ELSE
INCREASE ThisInvLocation.IL_Qty BY OLD_VALUE(PackageDetail.PD_Qty)-PackageDetail.PD_Qty
INCREASE ThisInventoryMaster.IM_Qty4Packed BY PackageDetail.PD_Qty-OLD_VALUE(PackageDetail.PD_Qty)

This rule covers, WAS ADDED TO, WAS REMOVED FROM and WAS CHANGED.

The logic I think I need to add to cover all conditions they can use to pull qty from and split into several processes if needed.

Example #1:

FIND InvLocation WHERE InvLocation.PM_PackageDetail.ID=PackageDetail.ID TAKE BEST 1
FIND InvExpDate WHERE InvExpDate.PM_PackageDetail.ID=PackageDetail.ID TAKE BEST 1
FIND InventoryMaster WHERE InventoryMaster.PM_PackageDtl.ID=PackageDetail.ID TAKE BEST 1

IF LooseQty with ExpirationDates
THEN...
ELSE IF LooseQty with no expiration dates
THEN...
ELSE IF LocationQty with expiration dates
THEN...
ELSE LocationQty with no expirations dates
THEN...

Example #2 Call separate process to managing each condition

FIND InvLocation WHERE InvLocation.PM_PackageDetail.ID=PackageDetail.ID TAKE BEST 1
FIND InvExpDate WHERE InvExpDate.PM_PackageDetail.ID=PackageDetail.ID TAKE BEST 1
FIND InventoryMaster WHERE InventoryMaster.PM_PackageDtl.ID=PackageDetail.ID TAKE BEST 1

IF LoostQty with ExpirationDates
THEN call process passing parameters

IF LooseQty with no expiration dates
THEN call process pass parameters

IF LocationQty with expiration dates
THEN call process pass parameters

IF LocationQty with no expirations dates
THEN call process passing parameters

Also, would it be a good idea to have 3 qty fields in the package detail BO and show all 3 fields on the Add/Edit form for package detail?
Loose Qty Packed ___________ short cut to Available Qty
LocationQty Packed _________short cut to Available Qty
Expiration Date Qty Packed_________short cut to Available Qty

There is a location combo box on the form that is filtered based on the Inventory # they enter. It will show the locations and qty for the selected item if there is any. I could do the same for an expiration date.

Thoughts?

rbross

RESOLVED 🙂

I figured out a solution that works to manage the 3 different inventory Qty's that can get changed when adding, removing or changing package detail qty.