Writing in Retirement

Short Ship Forecaster reads the Microsoft Dynamics (Great Plains) SQL database to calculate future inventory levels for future dates for sales inventory items and kit items based on current inventory levels, open sales orders and open purchase orders.  

The Great Plains Manufacturing modules are not required.  The program does not require any modifications to the Great Plains SQL tables, and data is never written any SQL tables.    

The GATHER DATA button reads the open sales orders and open purchase orders, and starts building in-memory lists of future transactions. 

The FORECAST BY SALES INVENTORY, and FORECAST BY DATE buttons will display the respective information in a data grid displayed on the screen.

The SHORT SHIP BY SALES INVENTORY and the SHORT SHIP BY DATE buttons will display only those items and only those dates when the future inventory is not sufficient to meet future sales orders.  

The program has logic to support different units of measure. 

Double clicking on any row in a data grid will show the underlining details. The data grid columns can be sorted by clicking on the column headers. 

The data in any currently displayed data grid can be written to Excel by clicking the GRID TO EXCEL button.  

Getting Started—Set Up Button

The top of the SET UP window sets the end date used for analyzing the SOP and POP transactions. This can be set one of two ways---the maximum date for all SOP and POP lines, or the number of weeks out.

The bottom of the SETTING window sets the SQL connection string.  This connection string is used by the software to access the SQL database server, and to select the database.  The software comes with a default connection string that is set to connect to a local server and the TWO test database.  

A SQL connection strings can be built many ways, depending upon the security of the SQL server.  A network engineer or SQL administrator can build the appropriate SQL connection string for each company.  The SQL connection string must be built for read-only access to the following production database tables and one SQL view:

• IV00101

• IV00102

• IV00105

• IV40201

• POP10100

• POP10110

• SOP10100

• SOP10200

• ItemQuantities (SQL view)

Both the end date and the connection string only need to be set once.  The program saves both as application properties stored on the user's PC.  

Detailed Processing Logic

The GATHER DATA button is used to collect open sales orders and open purchase orders from the Microsoft Dynamics Great Plains SQL tables.  Data is not collected from the historical tables, that is, transactions that have been posted. 

The starting date is always today's date, unless the database is the TWO sample database (uses April 12, 2017). 

Great Plains has many date fields in both Sales Orders and Purchase Orders. When the program is building the lists of activity by day and is calculating inventory positions by day, the program uses a new field called PROMISED DATE.  For Sales Orders, the REQUESTED SHIP DATE from the Sales Item Detail Entry screen is used as the PROMISED DATE.  For Purchase Orders, the CURRENT PROMISED DATE field from the Purchasing Item Detail Entry window is used as the PROMISED DATE.  

SOP and POP lines with a promise date greater than the SET END date from the SETUP screen are eliminated.

The software reads all open sales order lines for sop types ORDERS, INVOICES, BACKORDERS and FULLFILLMENT ORDERS, but not QUOTES and not RETURNS.    STANDARD and BLANKET purchase order types are included.  Drop Ship sales orders and purchase orders are not included.   

For both open sales order lines and open purchase order lines, only items of type “SALES INVENTORY” or “KITS” (from the inventory master table) are selected. Lines with item types “Discontinued Items,” “Misc. Charges,” “Services” and “Flat Fee” are not included.  

In the list of open sales orders and open purchase orders, for sale orders with kits, both the kit and each kit component is added to the list of open sales lines.   For the kit, the NAME field is the customer name, but for each kit component, the NAME field is “(component for ___ kit).”

Both sales and purchase orders can have partial fulfillment.  When a sales order invoice has a partial shipment, part of the transaction can be an INVOICE and part being a BACKORDER.  An open purchase order can have partial receipts.   In the data grid displaying Open SOP and POP Lines, both the QTY (the original document amount) and QTY REMAINING are shown, but only the QTY REMAINING Is used to calculate future inventory positions. 

For sales orders, NAME from the customer maintenance card based on the ID in the header of the sales order.  For purchase orders, the NAME comes from the vendor maintenance card, based on the vendor ID in in the purchase order header records.   

From the list of SOP and POP lines, a list of unique inventory items is created.  For each inventory item, the item master records are read, including various quantities, list price, base Unit of Measure and Unit of Measure Schedule. 

Great Plains has many quantity fields for each inventory item, including the concept of allocating (e.g., reserving) inventory for selected sales orders. 

This software ignores allocation and uses a different concept for computing future inventory positions.  The software starts by calculating a new field called “QTY WORKING”.  QTY WORKING is the inventory ON HAND summed across all sites, minus quantity IN USE, minus quantity IN SERVICE and minus quantity DAMAGED.  Quantity RETURNED is ignored.

The calculations of inventory forecasts by sales inventory starts with a list of the unique inventory items.  For each item in the list, a sub-list of all the days from today’s date through the end date is added.  All the SOP and POP transactions (for inventory items, within the time frame, and are not drop-shipped) are added to a sub-list attached to each day, and the inventory positions are calculated using base units of measure by item by day.  This list of items, with a sub-list of days, each day with a sub-list of transactions is the basis for the screens displayed by the FORECAST BY SALES INVENTORY button.    

                                  List of items

                                    For each item, list of days     

                                                List of transactions for each day

Next, the calculations of inventory forecasts by date starts with a list of all the dates between today’s date and the end date.  For each date in the list, a sub-list of all unique items is added. 

All the SOP and POP transactions (for inventory items, within the time frame, and are not drop-shipped) are added to a sub-list attached to each item, and the inventory positions are calculated using base units of measure by day by item.  This list of days, with a sub-list of items, each item with a sub-list of transactions is the basis for the screens displayed by FORECASE BY DATE button.    

                        List of days

                                    For each day, list of items     

                                                List of transactions for each item

Quantities are converted to the base unit of measure for all inventory position calculations. 

Restrictions

Inventory is summarized across all bins and locations---so an inventory shortage at one location would not be detected. 

There is no lag time built into the inventory calculations by day.  That is, purchases are assumed to be available for sales shipment on the day the purchase order is due in. 

Only sales inventory items are tracked.  All other items such as labor are not calculated.  

Inventory shortages are not tracked for kits, but for the kit components since GP does not track inventory for kits.    

Single currency only.