The Stock Reservations development is done in the project specific pi-reservations branch. This branch will be based on the 3.0MP13 release. Below are explained all needed developments to properly implement the reservations.
The reservations must be disabled by default. Only when they are enabled all new fields and processes should became visible to the users.
It is pending to define where and how are the reservations enabled and disabled.
Reservations management in database
Current stock implementation
Currently the stock information can be accessed in M_Storage_Detail and M_Storrage_Pending tables.
This table stores the stock information in the warehouse based on the stock dimensions (Product, Storage Bin, Attributes and Order UOM). For each combination of the dimensions it is stored the Quantity On Hand.
This table stored the information about Purchase and Sales order that have not been received or delivered. There is one record for each the following dimensions: Product, Attributes and Order UOM. In this case the Attributes are optional.
Reservations database implementation
The reservations are stored using two new tables: M_Reservation and M_Reservation_Stock.
In the M_Reservation table is defined the reservation. Who is the owner and what is reserved.
The owner can be a sales order line, a work requirement phase, a user or sales representative, customer and on hold. The on hold is used to reserve stock without assigning it to any owner, for example for quality inspections.
The reservation is always done for a Product in a Organization and some Quantity. It is also possible to set additional dimensions: Warehouse, Storage Detail and Attribute.
- Sales Order Line
- Sales order line owner of the reservation.
- Work Requirement Phase Product
- Work requirement owner of the reservation (when reservation in Manufacturing are implemented).
- Business Partner
- Customer owner of the reservation.
- Sales Representative owner of the reservation.
- Is Hold
- Flag to lock the product.
- Attribute Set Instance
- Fixed attribute set instance
- Storage Bin
- Fixed Storage Bin
- Fixed warehouse
- Quantity reserved.
- Order UOM
- Order Quantity
- Quantity reserved in the Order UOM
- Reservation Date
- Date when the reservation is done.
- Expiration Date
- Date until the reservation is valid.
In the M_Reservation_Stock table is set the actual reserved stock. When existing stock is reserved it is set the Storage Detail and the quantity. If it is a Pre Reservation the corresponding Purchase Order Line is set. If a storage detail is fixed and it is not desired to be used in any other reservation it is possible to Allocate it. Storage Details selected must fulfill the dimensions of the reservation.
- Storage Detail Id
- Reserved stock.
- Purchase Order Line
- Purchase Order Line for the Pre Reservation.
- Flag to set the stock as allocated.
- Quantity reserved.
- Order UOM
- Order Quantity
- Quantity reserved in the Order UOM
In order to ensure the correct usage of the reservations there are some mandatory conditions:
Reservations must be backed by stock
Any reservation must be backed by stock. The check must be done for any dimension.
When a product has an attribute set it has to be considered that the attribute set instance is optional in the Sales/Purchase Order. This means that when a sales order without attribute is reserved. It has to consider any reservation of the product with any attribute to calculate the available quantity. Also a Sales order with attribute predefined it has to consider reservations of that product with the attribute value and without any attribute.
check stock procedure
There must be a function with an easy way to check that all the reserved stock is backed by real stock. This function must check the available on hand quantity by each storage detail, by each dimension available and globally.
- The total on hand quantity of a product must be equal or higher than the reserved quantity.
- The total on hand quantity of a product in a warehouse must be equal or higher than the reserved quantity in the warehouse.
- The same applies for all the available dimensions: Warehouse, Storage Bin and Attribute Set.
By storage detail:
- The on hand quantity of a storage detail must be equal or higher than the reserved quantity assigned to that storage detail.
Update reservations on outgoing transactions
Whenever a new record is inserted in the m_transaction table consuming stock it has to be checked that related reservations are properly updated. An outgoing transaction of a reserved document must update and delete the reservation before the insert is done.
For each new record it has to be checked that its corresponding storage detail has reservations or not.
If it has reservations and the new on hand quantity falls below the reserved quantities these have to be updated to reserve other storage details. The all the reservations must be reallocated in new storage details using the get stock function and their fixed dimensions. If one of these reservations cannot be reallocated a not available stock error must be raised.
Stock retrieval on automatic processes
Current processes that need to pull stock from the warehouse have their own implementation to get the corresponding stock. All these processes need to be updated to use the M_Get_Stock function. At this moment the Generate Standard process of Manufacturing Management is the only one implementing this method.
The M_Get_Stock function will implement the reservation management and will only return stock not reserved or reserved for the document being created.
Reservation Management processes
Sales Order reservation
The reservations can be done from the sales order automatically when the order is booked or manually through a new button in the header.
The automatic reservation is done or not by a flag defined in the Sales Order line. The value of this flag can be defaulted on the order creation based on more generic configuration in Organization, Customer, Document Type, Product...
The reserve is done by default using the dimensions that are defined in the sales order: Warehouse and/or Attributes (only not instance-ables). These dimensions in case they are used would be automatically Fixed or Allocated in the reserve. The process will reserve all the available stock needed to fulfill the sales order using the get stock procedure with the available dimensions. This reservation does not take into account the minimum stock.
The process is launched by a button in the Sales Order header. It has as a parameter the type of reservation (Reservation or Pre-reserve) that is desired to be performed.
- It reserves the available stock using the get stock procedure and fixing or allocating the defined dimensions.
- It creates a Purchase Order for the not reserved quantity, even if there is available stock.
The button is shown when there is pending quantity to be reserved. In a case with not enough available stock if it is desired to do reserve the available stock and pre-reserve the remaining the button would have to be pressed twice, the first one to do the reservation of the available stock and a second one to create the pre-reservation order for the remaining not reserved quantity.
Sales Order Reservation Management window
New window where all Sales Order lines pending to be shipped are shown. For each line is visible the reservation status and it is possible to edit it through different buttons.
The following actions shall be possible from this window:
- Reserve and/or unreserve a sales order line.
- Add or remove fixed dimensions to a sales order line reservation.
- Assign existing Purchase Orders to Sales Order lines to create a Prereservation.
- Create Purchase Orders to create Prereservations for the selected Sales Order Lines.
Note: Mockups are needed to finish the definition of the capabilities of the window.
Stock Management window
New window/tab where an overview of the available stock is shown. It shows all the storage details that belong to a warehouse. For each storage detail are shown the related reservations and their fixed dimensions.
From this window the following actions should be possible:
- Reserve/Unreserve stock for a Sales Representative or Customer.
- Manage On Hold stock.
- Manage Allocated Dimensions.
- Move reserved stock to other Storage Bin/Warehouse?
Purchasing Plan (MRP)
The Manufacturing and Purchasing plan need to be updated to take into account reserved stock and documents.
Additionally, the Purchasing Plan might add a new button or modify existing Create Purchase Orders process to convert them into Preservations in case the client is using them.
When a Goods Receipt of a prereserve order is processed the status of the related reservations must be updated.
The M_InOut_Post procedure has to update the reserve from prereserve to reservation. The dimensions of the reservation have to be updated as well with the ones set in the Goods Receipt, specially Attribute and Storage Bin.
When a reserved Sales Order is delivered it has to be removed from the system.
The M_InOut_Post procedure has to delete the related reservations when a Goods Shipment of a reserved sales order is processed.
Other Transaction types
Material transactions that are not related to reservations must also take them into account.
After any stock consumption must be checked that reserved quantity is below the on hand quantity. This check can be done inside the check stock procedure or in a trigger in the Material Transaction Table.
Reservation Consumption process
Whenever a shipment related to a reserved sales order line is processed the reservation needs to be consumed to release reserved stock.
The reservation consumption is done following steps to cover all scenarios. Steps are done in order until all the quantity of the shipment line has been released.
- If the shipped stock's locator, warehouse or attribute is different than those defined as dimensions in the reservations an exception is thrown.
- If the pending quantity in the reserve is lower than the shipment quantity a warning message is set and only the pending quantity is released.
- Consume reserved stock if it's the same than the delivered stock (same locator and attributes)
- If there is still quantity to release and the quantity is higher than the not allocated reserved stock plus the pending to reserve quantity. This will require to unreserve allocated stock which is not possible so an exception is thrown
- Consume not reserved stock if there is available.
- This is done reserving the available stock and later releasing it. If the reserved quantity results higher than the quantity in the reserve not allocated reserved stock is unreserved.
- Consume other reservations not allocated stock. Only when the other reservation can be modified to reserve a different stock. The reserved stock is transferred to the current reservation and then released. The other reservation is re-reserved to recover the same reserved stock level.