Inventory Re-Order Report

Reports Menu -> Automatic Reporting -> Function RSPO20 -> runs Inventory Re-Order Report

This program can be used to print a Re-Order Report, or to generate a Re-Order Worksheet which can be edited in P.O. Worksheet before creating the actual P.O.'s from Generate P.O. From Worksheet.

This report can also be run automatically with the output emailed to staff by setting up RSPO20 with the relevant filters and email addresses in the Automatic Reporting.

This re-order process is by vendor and can be used when placing orders for specific suppliers.
Re-order requirements can be based on re-order minimum and maximum quantities or on past sales history. Other products from this vendor can also be identified and ordered at the same time.
If ordering items that don't come from the same supplier such as Party Disposables, listings for Product Class or for Group selection can also be generated.

A similar re-order report but with negative quantity P.O.s, can be generated for stock returns using the Slow Moving Inventory Report.

Determining the Best Price

The primary supplier can be identified for each product in Sales Inventory or Rental Inventory, with the Vendor number, name, Vendor's product #, Vendor's product description, and Vendor re-order pricing information including re-order price, order conversion factor and unit of measure.

Alternate Vendor Purchasing can be used to store that same product and re-order information for multiple other suppliers that your firm may use.
Alternate Vendor Purchasing can also be used to store different pricing for the same supplier using varying order conversions with different units of measure.
Pricing by Division can be further specified in Alternate Vendor Pricing By Division.

The alternate vendor pricing is respected when product quantities are ordered, first picking the product price eligible at the operator's default division, by choosing the lowest order conversion factor, and then the lowest price.
If there are no eligible division pricing records, then the company-wide product price using the lowest conversion factor is applied.
If no company-wide pricing applies, the Sales Inventory or Rental Inventory re-order price is used.

The Re-Order Price prints on the report, but for products with no Re-Order Price, the Last P.O. Cost times the Order conversion prints.

Determining Quantity to Re-order

When determining the current quantity available in stock, the calculation includes the quantity on hand plus the quantity on open Purchase Orders, then subtracts the quantity needed to fulfill open contracts and sales orders.
If the on hand quantity is actually a negative amount in the product file the negative quantity will print on the report, however a setting in the "Inventory Parameters" window of the Company Parameters, controls whether products with negative quantities on hand should be included in the re-order calculation as actually having negative quantities, or whether they should be considered to equal zero quantity on hand in the formula.

The quantity Committed includes any quantities out on Contracts, and any quantities back ordered on Sales Orders, and on Work Orders.
The Variance figure is the difference between what is actually required, and what is ordered because of a re-order multiple. e.g. need 7, but order 10, because order multiple is 5, and so variance is 3

If the option to Min/Max Or Sales History is M, it then uses either the Min/Max Inventory Quantity or the Re-order Multiple to determine the Suggested Re-order Quantity.
If the option to Min/Max Or Sales History is S, then the last 12 months average sales per the Sales History, is used to calculate the Re-order quantity.

Variance Definition:

The report usually recommends to re-order to meet the Maximum, but if a product has both a Maximum re-order quantity, and a re-order Conversion Multiple, the suggested re-order amount may exceed the maximum quantity in order to meet the vendor's order conversion multiple requirement.
The Variance figure on the report is the difference between what is actually required, and what is ordered because of a re-order multiple.
e.g. need 7, but order 10 because order multiple is in quantities of 5, and so variance is 3

P.O. Location Ordering:

The Re-Order Report can be done for all locations or for a specific branch or location.
The parameter Generate P.O. By Location From Worksheet in the Company Inventory Parameters can be used to control whether inventory re-order quantities are ordered at the location level for inventory flagged as Re-Order By Location by creating Purchase Orders for each location, or whether the total quantity is ordered on a P.O. from the operator's location.

The prompts to generate this report are:

GENERATE P.O. WORKSHEET

Uncheck this box to print the Re-Order report without using a worksheet.
The report then includes eligible products for all Purchasing Agents.

Check this box to print the Re-Order report and also to generate a Re-Order Worksheet that can be reviewed and modified in P.O. Worksheet.
The worksheet can be printed from Print P.O. Worksheet, and P.O.'s can be created to be printed or faxed from Generate P.O. From Worksheet.

WORKSHEET LOCATION

This field is skipped if the P.O. worksheet is not being generated.
If a worksheet is being generated type ALL or select a specific location from the drop-down box..

When the worksheet is generated for a specific location, multiple locations can generate their own worksheets at the same time without locking.

PURCHASING AGENT

This field is skipped if the P.O. worksheet is not being generated.
Enter an active Agent code or select it from the drop-down list as setup in Purchasing Agent Codes.
Only active Purchasing Agents can be assigned to a P.O.

Clear Existing Worksheet Confirmation

This pop-up prompt is only triggered when a P.O. Worksheet is being generated and if a worksheet already exists for this agent and this Location, that was created either from an earlier Slow Moving Inventory run or from the Inventory Re-Order Report.

Click the CANCEL button to save the worksheet and review it in P.O. Worksheet.
Click the CLEAR button to generate a new worksheet, replacing the existing one for this agent.

VALIDATE AGENT AGAINST VENDOR

Check this box to only select the products in the range that match the Vendor Purchasing Agent as setup in Vendor Information.

Uncheck this box to select any products in the range, regardless of the Purchasing Agent assigned to the Vendor.

FROM VENDOR#

Leave this field blank to include all vendors or enter a starting vendor number or select one from the Vendor Search window.

TO VENDOR#

Leave this field blank to include all vendors or enter an ending vendor number or select one from the window.

LOCATION, "ALL" OR "SEL"

This field defaults to ALL if no worksheet is being generated, or if worksheets are being generated it defaults to the selected Worksheet Location.
It is only accessible to be changed if worksheets are being generated for ALL locations, or not being generated in this run, and if the operator has permission to select locations as defined in Operators.

• Type ALL to include all locations in the report or worksheet.
• To print the report or worksheet for a single location, leave this field blank for the Head Office, or enter a specific location code, or select one from the drop-down list.
• Type SEL to select several specific locations or locations by inventory region, as outlined in Division Select By Region.

Note: When a single Location is select in this filter, the Include Non-reorder by Location Products option becomes enabled and can be unchecked as required.

PRODUCTS TO INCLUDE

Select one of the following product filter options:

• Select Below Minimum to only include products when the available quantity is less than the minimum inventory quantity required as setup in the Re-Order Information for Sales parts, and in Make/Model and Re-Order Information for Rental assets.

• Select Backordered to include only backordered products.
  The Backordered option requires that the re-order Min/Max calculation be used as determined in the Re-Order Quantity Based On selection parameter below.

• Select All Products to include all products assigned to the selected vendors.
  Using this option, other products from this vendor can also be identified and ordered at the same time.

RE-ORDER QUANTITY BASED ON

This selection applies only to products that are NOT ordered by location.
Any Rental products 'ordered by location' always use Min/Max and any Sales parts 'ordered by location' use the selection for that product for that location in the Re-Order Information window for the product.

Select one of the following options for products not ordered by location:

• Select Min/Max to calculate the quantity available as the quantity on hand plus the quantity on open Purchase Orders, less the quantity committed to fulfill open contracts and sales orders.
  The Re-order report checks the available quantity and when a shortage is identified, it will recommend to re-order to meet the Maximum re-order amount.
  If there is no Max quantity for a product, the Minimum will be ordered as defined in the Re-Order Information or Make/Model and Re-Order Information window.
  If there is no Min or Max for a product, the re-order quantity will match the shortage.

  Note: The Min/Max option is always used to calculate re-order quantities when the product selection is for Backordered products.

• Select Sales History to determine the re-order quantity by averaging the quantities from the Sales History over the last 12 months (excluding current month).

• Select Daily Average to determine the re-order quantity by averaging the quantities in Sales History over the specified # Days Average To Use.
  This calculation also includes parts on open Work Orders.

  # DAYS AVERAGE TO USE

  Type 30 to use a 30 day average in the calculation, or entered a preferred average number of days to be used. This applies only to the calculations based on Sales History.

Note: If any products on the report are ordered by location, the report heading is always Min/Max instead of Min/Sales, but any products that are not ordered by location and are using Sales History averages can be identified as they will print "Sales" under the Min column and will print the Average Sales amount under the Max column.

ORDER CODE

Accept ALL to include products with all Order Codes or select one from the Order Code Search window.

INVOICE ACTIVITY SELECTION

The parameters in this Invoice Activity window can be used to filter products that are not active based on an invoice count within a set time period.
This check helps prevent re-ordering a product that had a one time large quantity on a special order.

USE MIN # OF INVOICES

Uncheck this box to skip the re-order quantity check for invoices in a date range.

Check this box to check for a set number of invoices in a date range.
If the required number of invoices in the time frame selected was not met, the product will be excluded from the re-order report regardless of Min/Max or Sales History.

MINIMUM # OF INVOICES

Enter the number of invoices required in the date range to include the product.

FROM INVOICE DATE

Enter a starting date for the invoice checking range.

TO INVOICE DATE

Enter an ending date for the range.

Finished?

Click OK to exit the window.

INCLUDE NON-REORDER BY LOCATION PRODUCTS

This field defaults to Yes, and is only enabled providing the ability to uncheck the box, if a single Location has been selected in the Location, "All" or "SEL" filter above.

Check this box to include all eligible products regardless of whether they are flagged to Re-Order by Location.

Uncheck this box to only include products that are Re-Ordered by Location as activated by the flag in the Inventory Re-Order Information window for Sales products and in the Make/Model and Re-Order Informational Assets window for rentals.

Products that are Re-Ordered by Location show the ON HAND, ON PO, QTY COMMIT and BIN values at the location level instead of company wide.
These products are identified with an asterisk (*) beside the product description on the report output, and in the 'Re-Order by Location' column on the spreadsheet output.

PRODUCT SELECTION

The fields in this window can be used to specify and filter the products in the report or worksheet.

PRODUCT TYPE

Select one of the following product options:

• Select Rentals to include only Rental Equipment in the re-order product list.
• Select Sales to include only Sale Parts in the re-order product list.
• Select Both to include Sale and Rental Products in the re-order product list.

Note: Non-Bulk Rental equipment will be omitted as there is only ever quantity one per product#.

FROM PRODUCT CLASS

Leave this field blank to include all Classes or enter a starting Product Class or select one from the Product Class Search window.
If ordering items that don't come from the same supplier, such as Party Disposables, listings for a Product Class or for a Group can be generated.

TO PRODUCT CLASS

Leave this field blank to include Classes or enter an ending Product Class or select one from the window.

FROM GROUP

Leave this field blank to include all Groups or enter a starting Product Group or select one from the Group Search window.

TO GROUP

Leave this field blank to include all Groups or enter an ending Product Group or select one from the window.

Finished?

Click OK to exit the Product Selection window.

PRINT VENDOR PRODUCT #

The report prints your company's product number and description.

Check this box to print a second detail line for each product, listing the vendor's product number and description too.
Uncheck this box to omit the vendor's product number and vendor's product description.

PRINT ALT. QTY AVAILABLE

This option does not apply and is disabled if the report is to be printed for ALL locations.

If the Re-order report is being generated for specific locations, an alternate quantity available total (i.e. a surplus quantity above the maximum re-order quantity at another location), can be printed on the report for the selected locations.
This helps to determine that instead of ordering more stock, perhaps a transfer of the surplus could be arranged from the other location.

Uncheck this box if an alternate quantity available at another location is not to be printed on the report.

Check this box to include alternate quantities that are available at other locations.
Specify which other locations should be checked and included for alternate surplus quantities on the report.

LOCATION, "ALL" OR "SEL"

• Type ALL to include all locations in the surplus quantity totals.
• To print the surplus for a single location, leave this field blank for the Head Office, or enter a specific location code, or select one from the drop-down list.
• Type SEL to select several specific locations or locations by inventory region, as outlined in Division Select By Region.

PRINT COMMITTED DETAILS

Check this box to print the committed product totals, and the document details for each committed product in this report.
Committed product details include the type of document, document number, customer and also the asset being serviced if the part is committed on a Work Order.

Uncheck this box to print the committed product totals, but to suppress the document details for each committed product in this report.

Note: This prompt is unchecked and cannot be changed if the output is being exported to an Excel spreadsheet.

SUBTOTAL BY

Select one of the following sort and subtotal options to apply within the vendor sort on the report output.
The Class/Group sort information is also respected on the spreadsheet but subtotals are not supplied.

• Select Class to sort and subtotal the products by Product Class with the class description, for each vendor.
  Group information will be omitted.
• Select Group to sort and subtotal the products by Group with description.
  Product Class information will be omitted.

SORT VENDORS BY

Subtotals by vendor are supplied on the report output but not on the spreadsheet.
Select one of the following vendor sort options:

• Select Name to sort and print the report alphabetically by the Vendor name.
• Select Number to produce the report in Vendor number sequence.

SORT PRODUCTS BY

This product sort selection applies within the vendor sort, and within the Class/Group sort.
Select one of the following sort options including:

• Product Number
• Description
• Vendor Product Number
• Vendor Product Description

Note: The 'Bin#' value, only applies to products that are "Ordered by Location".
The 'Our Product Description' is printed on the output, even when the Re-Order Report is sorted by 'Vendor Product Description'.

REPORT OPTIONS

This value defaults to the operator preference as defined in Operators.
Select one of the output options to Print a report or export the data to Excel as outlined in Report Options.

Finished?

Click the ACCEPT button to generate the report, or CANCEL to abort.

Generate Purchase Orders Using Re-Order Worksheet:

If a Re-order Worksheet has been generated, it can be reviewed and modified in P.O. Worksheet.
The worksheet can be printed from Print P.O. Worksheet.
P.O.'s can be created, and printed or faxed from Generate P.O. From Worksheet.
The parameter Generate P.O. By Location From Worksheet in the Company Inventory Parameters controls whether the resulting Purchase Orders for inventory flagged as Re-Order By Location are created for each location, or whether the total quantity is ordered on a P.O. from the operator's location.

Products that are Re-Ordered by Location show the ON HAND, ON PO and QTY COMMIT values at the location level instead of company wide.
These products are identified with an asterisk (*) beside the product description on the report output, and in the 'Re-Order by Location' column on the spreadsheet output.

Note: If a Rental or Sales product is included on the Re-Order Worksheet as it is below the minimum, a Purchase Order will not be created if the Stop P.O.s flag has been set in the Make/Model and Re-Order Information (Rental Assets) or the Inventory Re-Order Information (Sales Products) window.