Sage 100

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

I feel like that might not be enough, i.e. if the item was out of stock Feb15-Mar 12, then then again June 1-July 17, etc. and they want at the end of the year to know it was out of stock 75 days, for example.

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

I feel like that might not be enough, i.e. if the item was out of stock Feb15-Mar 12, then then again June 1-July 17, etc. and they want at the end of the year to know it was out of stock 75 days, for example.

------------------------------
Beth Bowers
(269) 358-0989
------------------------------


Original Message:
Sent: 01-06-2025 02:19
From: Dan Burleson
Subject: Out of Stock Report

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

To my db development ears, this doesn't strike me as just a report problem, because of multiple occurrences issue. 

Also, using a report like they propose will likely raise more, related questions for reporting.

If I were you, I'd offer the straight report, like Amber proposes, but also an alternative: create a custom data for daily stock levels of each sku (one line for each date and each sku in one column), created in a daily BOI/VI batch. Now you can run all sorts of reports (SI or DataSelf) about the issue at any time.

If stockouts is a real problem, they should want to deal with them ASAP. So, SAW would help raise alarms.

If they think they have issues with supplier performance, reports about PO promised vs received would help.

What is the underlying problem they are really trying to fix? 

I feel like that might not be enough, i.e. if the item was out of stock Feb15-Mar 12, then then again June 1-July 17, etc. and they want at the end of the year to know it was out of stock 75 days, for example.

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

@Jerry Norman - this is a sales defense mechanism - "We COULD have sold them if we had them".  Unfortunately, all of their product comes from overseas, so flagging the stock out when it happens isn't going to do much - the blame goes back to their parent company who isn't providing them with product.

To my db development ears, this doesn't strike me as just a report problem, because of multiple occurrences issue. 

Also, using a report like they propose will likely raise more, related questions for reporting.

If I were you, I'd offer the straight report, like Amber proposes, but also an alternative: create a custom data for daily stock levels of each sku (one line for each date and each sku in one column), created in a daily BOI/VI batch. Now you can run all sorts of reports (SI or DataSelf) about the issue at any time.

If stockouts is a real problem, they should want to deal with them ASAP. So, SAW would help raise alarms.

If they think they have issues with supplier performance, reports about PO promised vs received would help.

What is the underlying problem they are really trying to fix? 

I feel like that might not be enough, i.e. if the item was out of stock Feb15-Mar 12, then then again June 1-July 17, etc. and they want at the end of the year to know it was out of stock 75 days, for example.

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

I feel like that might not be enough, i.e. if the item was out of stock Feb15-Mar 12, then then again June 1-July 17, etc. and they want at the end of the year to know it was out of stock 75 days, for example.

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

I feel like that might not be enough, i.e. if the item was out of stock Feb15-Mar 12, then then again June 1-July 17, etc. and they want at the end of the year to know it was out of stock 75 days, for example.

A V/I job or BOI script could enable this and wouldn't be restricted to month. Just how frequently they run the I/M Trial Balance report or some other report with a table having ItemCode and Warehousecode.

A BOI script could run daily from the Task Scheduler and check the IM_ItemWarehouse QuantityOnHand field, and set an out-of-stock UDF in the same table with the date if it is found to be zero, and not already having an out of stock date, or clear dates for those found to have a positive QuantityOnHand. 

A V/I job could do this as well, but would be slower. A BOI script could also maintain a log file of items and the dates that they run out. The out-of-stock UDF date field could be added to the I/M Trial Balance Work table to report on ItemCode by WarehouseCode. 

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

Hi Beth - I created a quick draft from the Item Transaction History table to get the most recent Out-of-Stock Date but would have to think about how to do this for previous dates if that is what is needed. Would this work for your client as is?

In case it's of any value, here's a report I had created a ways back.

Hi Beth - I created a quick draft from the Item Transaction History table to get the most recent Out-of-Stock Date but would have to think about how to do this for previous dates if that is what is needed. Would this work for your client as is?

I had a client moving off Inventory Advisor and needed this exact thing. I ended up creating something similar to what Dan is suggesting with BOI script that checks every day to see what is out of stock and if it finds something, it writes it as an "out" record to a UDT.  A second BOI script runs a bit later and starts with the UDT and compares that to IM_ItemWarehouse and if there are any "out" records that now have quantity on hand, an "In" record is created in the UDT.  The end result is a report that shows the time periods when an item was out of stock for whatever date range they wanted.  The BOI scripts run in the middle of the night because the IM_ItemWarehouse table is huge.