| T O P I C R E V I E W |
| victor |
Posted - 10/21/2009 : 8:52:06 PM
When adjusting inventory we've seen that the FIFO quantity does not automatically adjust to the same amount as "# On Hand". Here's an example: I have 8 units of Product-A physically on the shelf. I bought each for $5.00. SEOM shows 10 units on hand so I remove 2 through the Adjust QOH button. Next, I click on the FIFO Status button and it shows 10 units at $5.00 each. There a quite a few problems that this will cause over time. How do we keep both On Hand quantity and FIFO quantity equal? Is this a bug or am I doing something wrong? |
| 5 L A T E S T R E P L I E S (Newest First) |
| kevin |
Posted - 10/26/2009 : 08:33:05 AM
quote:
[i]Originally posted by dynamitetech[/i]
[br]Stephen Cisick from Technology Co-Pilot wrote a query that will check the FIFO table and compare it to the QOH table and report the difference. You run this query daily and then when you find a discrepancy, you can research what happened on that day with that item to make the difference.
The v5.9 series of the Order Manager contains a new Inventory report that provides this information. |
| geckoday |
Posted - 10/24/2009 : 11:13:42 AM
quote:
[i]Originally posted by altaireon[/i]
[br]Communique release 7 has an On Order Discrepancy report located in the Data -> SKUs section. It will compare your InventorySuppliers.OnOrder with the POHistory.Quantity and report any discrepancies.
Or for the more technically inclined, you can run the following query
SELECT
Inventory.LocalSKU,
Inventory.ItemName,
InventorySuppliers.SupplierSKU,
InventorySuppliers.OnOrder,
Sum(POHistory.Quantity) AS QtyOnOrder
FROM Inventory
INNER JOIN (InventorySuppliers
INNER JOIN POHistory
ON (InventorySuppliers.LocalSKU = POHistory.LocalSKU)
AND (InventorySuppliers.SupplierSKU = POHistory.SuppliersSKU))
ON Inventory.LocalSKU = InventorySuppliers.LocalSKU
WHERE (POHistory.Type = "E")
GROUP BY
Inventory.LocalSKU,
Inventory.ItemName,
InventorySuppliers.SupplierSKU,
InventorySuppliers.OnOrder
HAVING (Sum(POHistory.Quantity) <> [OnOrder])
This thread is about QOH vs FIFO, not QOO vs PO on order so this is useless for the topic at hand. Even more so since it won't even do the job you claim it does. Between the INNER JOINS and the WHERE clause it will not report any SKU's with a QOO where there are no PO expected records. You need a LEFT JOIN in there to make sure all SKU's are accounted for. Not only that you need to account for any PO History records for SKU's that aren't in the SKU table. That will require a UNION to another query. Not only that joining on the suppliers SKU is wrong as PO's can override the supplier SKU and therefore be different than what is in the Inventory Suppliers table yet still be part of the QOO for that SKU/Supplier. You need a join to the Purchase Orders table to get the suppliers ID for the PO History record.
To compare FIFO to the QOH in the Inventory table and report all differences the proper query is:
SELECT *
FROM (SELECT FIFO.SKU, FIFO.QOH-Inv.QOH AS QOHDiff
FROM SnowRiverFIFOSKUSummary AS FIFO INNER JOIN InventoryQuery AS Inv ON FIFO.SKU = Inv.SKU
UNION ALL
SELECT FIFO.SKU, FIFO.QOH AS QOHDiff
FROM SnowRiverFIFOSKUSummary AS FIFO LEFT JOIN InventoryQuery AS Inv ON FIFO.SKU = Inv.SKU
WHERE Inv.SKU IS NULL
UNION ALL
SELECT Inv.SKU, 0-Inv.QOH AS QOHDiff
FROM SnowRiverFIFOSKUSummary AS FIFO RIGHT JOIN InventoryQuery AS Inv ON FIFO.SKU = Inv.SKU
WHERE FIFO.SKU IS NULL
) AS FIFODiff
WHERE FIFODiff.QOHDiff<>0
ORDER BY FIFODiff.SKU;
|
| altaireon |
Posted - 10/23/2009 : 10:49:41 PM
Communique release 7 has an On Order Discrepancy report located in the Data -> SKUs section. It will compare your InventorySuppliers.OnOrder with the POHistory.Quantity and report any discrepancies.
Or for the more technically inclined, you can run the following query
SELECT
Inventory.LocalSKU,
Inventory.ItemName,
InventorySuppliers.SupplierSKU,
InventorySuppliers.OnOrder,
Sum(POHistory.Quantity) AS QtyOnOrder
FROM Inventory
INNER JOIN (InventorySuppliers
INNER JOIN POHistory
ON (InventorySuppliers.LocalSKU = POHistory.LocalSKU)
AND (InventorySuppliers.SupplierSKU = POHistory.SuppliersSKU))
ON Inventory.LocalSKU = InventorySuppliers.LocalSKU
WHERE (POHistory.Type = "E")
GROUP BY
Inventory.LocalSKU,
Inventory.ItemName,
InventorySuppliers.SupplierSKU,
InventorySuppliers.OnOrder
HAVING (Sum(POHistory.Quantity) <> [OnOrder])
|
| dynamitetech |
Posted - 10/23/2009 : 6:02:34 PM
Stephen Cisick from Technology Co-Pilot wrote a query that will check the FIFO table and compare it to the QOH table and report the difference. You run this query daily and then when you find a discrepancy, you can research what happened on that day with that item to make the difference. |
| kevin |
Posted - 10/22/2009 : 09:52:47 AM
Did you check the FIFO QOH prior to the adjustment and after the adjustment to ensure that FIFO did not change when the adjustment was made? If so, then yes, this is a bug I would need to review assuming TrackInventoryFIFO parameter was not set to False.
Keep in mind that FIFO and QOH are values calculated separately. In normal operations the two should always agree regarding the on hand amounts, however, errors encountered during operations could cause discrepancies to arise. Should the QOH maintained in the Inventory table be adjusted but an error is encountered prior to the FIFO table being adjusted, the two values will be out of synch. Manual adjustments directly in the tables will also have the same effect if both tables are not altered by the same amount. |
|
|
