Sales Reservations by Location

 

Overview

Currently xTuple ERP supports a Sales Reservations functionality that allows inventory in a warehouse to be reserved against a sales order. This is designed to help reduce conflict in picking and shipping departments where two sales orders may require the same item, but there is only enough on hand to fulfill one order.

The existing Sales Reservations functionality available in xTuple ERP 3.0 must be expanded to be able to reserve quantities at the location level and print these location allocations on a pick list. This will reduce the possibility of conflict where items are stored in multiple locations in a warehouse but there is more demand than supply in any one location at a given time. With allocation by location pickers will not find themselves in a situation where they are searching many locations to find enough quantity to fulfill an order, which may or may not have been picked by another open order, but instead are guided to specific locations where quantities have been reserved for the order. This is especially important in environments where there are many orders for the same item at any given time, and there is a significant lag time between the time items are picked from inventory and when the picking is recorded in the database.

Allocation by Location will be accomplished automatically when a system configuration for this behavior is turned on and an item being reserved is location controlled. The order of precedence for location allocation will be determined by configuration settings. Since it is generally not practical to allocate inventory by locations manually, no manual interface to allocate by location will be provided.

Related Existing Functionality

This functionality will leverage existing Multiple Location Control and Sales Reservation facilities in xTuple ERP Standard and OpenMFG Editions.

User-Level Functionality

Window Changes

configureSO.png

The Configure Sales window will be modified as shown to in include a new option to "Allocate by Location." Users will be able to choose the sequence by which allocations will be made in order of Lowest quantity available, highest quantity available or alpha-numeric order by the location name. The modified UI file may be found attached to this page. When the reservations are made manually or the Allocate Reservations utility is run, this setting will determine whether and how reservations will automatically be made by location. These new options will only be available on xTuple ERP Standard and OpenMFG editions.

When allocate by location changes from a state of being enabled to disabled, the user should be warned that all location reservations will be removed, then a statement should be run to clear the location reservation table.

From the Sales Order and Inventory Availability by Sales Order and Customer displays users can currently right click "Show Reservations..." and see all the sales order allocations by item in an Item Reservations window. That window will be modified to include an indented view that displays location reservations beneath the Sales Order line if applicable.

When allocation by location control is enabled, two new columns will be visible in the inventory distribution window: Reserved and This Reserve, where Reserved shows the total quantity reserved in the listed location, and This Reserved shows the quantity reserved for the order being distributed to. Users will not be allowed to distribute more quantity than is available which is calculated as Qty Before - Reserved + This Reserved.

Developer Note: Determining this information will require tracing back to the reservation record through the inventory history transaction to the original sales order.

There needs to be some mechanism by which to view allocations made at the location level for troubleshooting and audit purposes. When Sales Reservations by location is enabled, a column should appear in the Quantities on Hand by Location window that shows the reserved quantity at the location. Lines that have reserved quantities should have a right click option to open a new Location Reservations window that compliments the Item Reservations window, but shows all sales order reservations by a given location rather than a given item.

Report Changes

The printed report for the Quantities on Hand by Location screen would need to be modified to print reservations when Sales Reservations is enabled.

The sales order pick list needs to be modified to show a list of allocated locations by line item. Because of grouping issues, this problem is probably best solved by using a function to return a text block concatenated with carriage returns.

Problems and Alternatives

The biggest potential problem anticipated with this feature is the likelihood for confusion as to what is reserved where. The screens and report modifications above should provide basic visibility, but may not always be in the most intuitive location. For example a user may not expect to find reservation by location information in an inventory report when most reservation information is found in sales. However, it seems superfluous to build a special report for this purpose, or add it to the sales menu which would only add clutter and bloat to the application. The best thing to do is to document sales reservations in a wiki topic and train users well on the location of relevant reports. Alternatively, users could add or move windows with scripts if deemed necessary.

The real world often does not agree with the computer. A user may be told items are reserved on shelf A only to find they were moved to shelf B without the move being recorded. By not providing a way to manually allocate to locations users will basically have to trust that the computer to reserve locations accurately with no way to manually intervene. This may appear to limit flexibility. However, it is hard to imagine users expending the effort to reserve inventory by location manually. If they know inventory was manually picked from a location other than the one that it was reserved to, they would either simply remove the reservation and conduct the transaction based what actually happened, or record movement of inventory to the location prescribed so the issue transaction may be completed. Either way it is unnecessary to manually re-reserve the inventory, so it seems unnecessary to add that capability.

Because location control is intimately associated with lot/serial control, a side affect of location reservations is that items that are both location controlled and lot/serial controlled will be reserved at the lot/serial level. There should be no negative side effects of this other than perhaps the question of what sequence lot/serial numbers should be allocated in. Selecting lot/serial number in order of lowest perishable date, warranty date and primary key will effectively cause lot/serial items to be reserved in a FIFO order, which is likely to be the most desirable behavior. Otherwise, the ability to control this behavior manually or in any other sequence is outside the scope of this specification.

Another side affect is that unlike sales reservations at the warehouse level, reservations at the location level will be enforced for all transaction types, not just shipping. Generally this is a good thing as it makes system wide enforcement more consistent.

Internal Design

Basic Algorithms

Reserving Items

The reservesolineqty function would be altered to include the following additional logic:

Execute existing algorithm
Add In....

If item site is location controlled
  Fetch metric to determine whether to allocate locations
     If metric is allocated by lowest qty
        Loop through list of netable locations for current item site sorting by qty ascending
          available = item location qty - sum(item location reserved qty)

          If available is less than S/O qty to reserve then
            location to reserve = available
          Else
            location to reserve = S/O qty to reserve

          Insert a record into item location reserve table recording reservation
     Else If metric is allocated by highest qty
        Same loop above except sorting by highest first
     Else
        Same loop above except sorting by location name alpha numeric

...continue existing algorithm

Unreserving Items

Currently sales order line items are unreserved by simply setting the reserved quantity on a sales order line item to zero. Allocation by Location adds more complexity that will require a new function unreservesoline that works as follows:

PASS IN coitem_id

  Delete all location reservations for this sales order item
  Update coitem reserved quantity to zero

RETURN true

The unreservesoline function will be available in Standard and OpenMFG editions only.

When sales order line item status is changed to canceled or closed the unreservesoline function should be called to make sure there are no invalid reservation records outstanding.

Reservation Distribution

When items are issued to sales orders, any quantities associated with location reservations should be deducted from the reservation record. Currently there are two functions called in the Distribute Inventory window to commit inventory distributions: distributetolocations and postitemloc series. Both would need to be modified to deduct issued quantities from reservation records where applicable. The following logic would be added to these functions:

Execute existing algorithm
Add In...

If allocation by location is enabled
  For each itemlocdist record distributed
    Trace back inventory history to select a matching reservation for the item location and order item being processed
    If one is found
      If the quantity reserved is greater than the quantity issued on the current distribution
        Update the reservation record and reduce the reserved qty by the qty being distributed
      Else
        Delete the reservation record
      Delete

...continue existing algorithm

Printing Location Reservations

Printing location reservations on a sales order pick list may be problematic because of already complex grouping requirements on that report. The problem is probably best solved by creating a function called getpicklistlocrsrv that returns concatenated location reservation information as a text block:

PASS IN coitem_id,number

Declare variable "result" as text

--Build a header
Result =  'Location' + calculated number of spaces + 'Quantity'

--Build the rows
Loop through each location reservation for the sales line
  Result += Carriage Return || Location Name ||  calculated number of spaces ||  formated Quantity

RETURN result

The number passed in is the width of the location name. It should be used to make sure location name is fixed width so printed result looks tabular even if the location names have varying lengths. For Example:

Location

Qty

A-10-C

10

B-9-C

1

B-110-C

1

Schema Changes

A new table to store location reservation information should be created that is only available in xTuple ERP Distribution and OpenMFG editions.

The new itemlocrsrv table

Field

Description

type

Comments

itemlocsrv_id

Primary Key

integer

 

itemlocsrv_coittem_id

Foreign Key

integer

Sales Order Item that location quantity is tied to

itemlocrsrv_itemloc_id

Foreign Key

integer

Item location that the quantity is tied to

itemlocrsrv_qty

Quantity

numeric

Quantity reserved

Performance Considerations

Using a join on the inventory history table to trace back to sales order location reservations may be inefficient because the joining will have to be done on the order number and order type which are not indexed. On large databases this could make for slow transactions. It may be a good idea to add an index to those columns.

Error Handling

It may be possible through back end manipulation or development oversights to set a reserved quantity on the sales order line that does agree with the total quantities reserved at the location level. It may be desirable to add a trigger to prevent this situation for location controlled items when location allocation is enabled. This could reduce the potential of having to make "fix" utilities in the future to correct this problem if it surfaces.

QA Considerations

Testing this functionality should be straight forward. Special attention should be made to make sure new allocation rules are enforced, the pre-existing non-location allocation mechanism is not negatively impacted, and pre-existing location transaction behavior does not change, particularly in PostBooks® which will not have necessary tables to support any of the sales reservation logic.

Distribution of reservations should be tested on a database with a large number of inventory history records to make sure performance is acceptable.

Documentation Considerations

The impact on reference documentation is moderate, but a wiki topic on Sales Reservations should be written as the over all functionality is becoming more complex.

Release Considerations

This feature has been committed to the sponsor for the 3.1 release of OpenMFG.

AttachmentSize
configureSO.zip4.65 KB