Projects:Warehouse Stock Management

Contents 1 Status 2 Definition 2.1 Feature overview 3 Current Status 3.1 Receipts / Shipments 3.1.1 M_INOUT_CREATE procedure 3.1.2 Other processes that create receipt / shipments 3.2 Goods Movements 3.3 Physical Inventories 3.4 Internal Movements 3.5 BOM Manufacturing 3.6 Manufacturing 3.7 Summary 4 Proposed Solution 4.1 M_STOCK_AUX table 4.2 M_UPDATE_STOCKAUX procedure 4.3 M_PRODUCTIONLINE_TRG trigger 4.4 M_TRANSACTION_TRG trigger 4.5 M_STOCK_PROPOSED table 4.6 M_GET_STOCK procedure 5 Customize Stock Proposal

Status

• Stage: Definition
• Planned Release: MP5
• Status Notes: Proposal

Definition

Currently each flow manages the warehouse movements in its own processes. This makes difficult to mantain and to implement new features. The aim of this project is to centralize this movements on a single api. This api shall provide methods to take stock from the warehouse and to store in them. This methods shall be configurable by rules, in order to be able to retrieve products by FIFO, LIFO, etc.

Feature overview

• Common method to retrieve products from stock based on rules.
• User configurable set of rules to manage stock.

Current Status

Current warehouse management by flow:

Receipts / Shipments

M_INOUT_CREATE procedure

Procedure that creates receipt / shipments. Gets the stock using the cur_storage cursor.

CURSOR Cur_Storage (p_Product varchar, p_Warehouse varchar, p_UOM varchar, p_Product_UOM varchar, p_Attribute varchar) IS
  SELECT T.C_UOM_ID, T.M_LOCATOR_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCT_UOM_ID,
         COALESCE(T.QtyOnHand, 0) AS QtyOnHand, T.QtyOrderOnHand AS QtyOrder
  FROM M_STORAGE_DETAIL T
           LEFT OUTER JOIN M_ATTRIBUTESETINSTANCE A ON T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
       M_LOCATOR L
  WHERE T.M_PRODUCT_ID=p_Product
    AND T.M_LOCATOR_ID=L.M_LOCATOR_ID
    AND L.M_WAREHOUSE_ID=p_Warehouse
    AND T.C_UOM_ID=p_UOM
    AND COALESCE(T.M_PRODUCT_UOM_ID, '0')=COALESCE(p_Product_UOM, '0')
    AND COALESCE(T.M_ATTRIBUTESETINSTANCE_ID, '0')=COALESCE(p_Attribute, COALESCE(T.M_ATTRIBUTESETINSTANCE_ID, '0'))
    AND COALESCE(T.QtyOnHand, 0)>0
  ORDER BY L.PRIORITYNO, A.CREATED, T.CREATED;

This cursor returns records from the M_Storage_Detail table ordered by the locator priority and the creation date of the attribute and storage detail row.

Other processes that create receipt / shipments

Other processes get the stock based on the order / invoice definition and selected Locator.

Goods Movements

Stock is selected using the product selector, new locator is selected manually.

Physical Inventories

Gets the stock from the M_Storage_Detail filtering the results by Product value, Locator, Warehouse, Product Category and ABD category, all of them being optional. Later it also filter out some records based on the quantity.

FOR Cur_Storage IN
 (SELECT s.M_Product_ID, s.M_Locator_ID,
         COALESCE(s.QtyOnHand, 0) AS QtyOnHand, (s.QTYORDERONHAND) AS QtyOnHandOrder,
         s.C_UOM_ID, s.M_Product_UOM_ID, s.M_AttributeSetInstance_ID
  FROM M_Product p
           INNER JOIN M_Storage_Detail s ON (s.M_Product_ID=p.M_Product_ID)
  WHERE p.AD_Client_ID=v_Client_ID  --    only ..
    AND(v_ProductValue IS NULL OR UPPER(p.Value) LIKE v_ProductValue)
    AND(v_Locator_ID IS NULL OR s.M_Locator_ID=v_Locator_ID)
    AND(v_ABC IS NULL OR v_ABC IN (SELECT abc FROM m_product_org po WHERE po.m_product_id = s.m_product_id ))
    AND(v_Warehouse_ID IS NULL  OR s.M_Locator_ID IN
        (SELECT M_Locator_ID FROM M_Locator WHERE M_Warehouse_ID=v_Warehouse_ID))
           AND(v_Product_Category_ID IS NULL
               OR p.M_Product_Category_ID=v_Product_Category_ID)
           AND NOT EXISTS
       (SELECT *
        FROM M_InventoryLine l
        WHERE l.M_Inventory_ID=v_Record_ID
          AND l.M_Product_ID=s.M_Product_ID
          AND l.M_Locator_ID=s.M_Locator_ID
        )
  ORDER BY s.M_Locator_ID, p.Value, s.Created)

Internal Movements

Stock to remove is manually selected from the product selector.

BOM Manufacturing

To get the stock it uses the cur_stock cursor of M_Production_Run procedure. This cursor filters the m_storage_detail by product, uom and the organization of the warehouse. It orders the result by warehouse, locator priority, creation date of the attribute set instance and storage detail record and finally by m_product_uom_id.

CURSOR CUR_STOCK (Product_ID VARCHAR, UOM VARCHAR, Warehouse_ID VARCHAR, Org_ID VARCHAR) IS
  SELECT T.M_PRODUCT_ID, T.C_UOM_ID, T.M_LOCATOR_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCT_UOM_ID,
         COALESCE(T.QtyOnHand, 0) AS Qty, T.QtyOrderOnHand AS QtyOrder
  FROM M_STORAGE_DETAIL T
           LEFT OUTER JOIN M_ATTRIBUTESETINSTANCE A ON T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
       M_LOCATOR L, M_WAREHOUSE W
  WHERE T.M_PRODUCT_ID=Product_ID
    AND T.C_UOM_ID=UOM
    AND T.M_LOCATOR_ID=L.M_LOCATOR_ID
    AND COALESCE(T.QtyOnHand, 0)>0
    AND L.M_WAREHOUSE_ID = W.M_WAREHOUSE_ID
    AND ad_isorgincluded(Org_ID, W.AD_ORG_ID, W.AD_CLIENT_ID) <> -1
  ORDER BY (CASE L.M_WAREHOUSE_ID WHEN Warehouse_ID THEN 1 ELSE 2 END),
      L.PRIORITYNO, A.CREATED, T.CREATED, T.M_PRODUCT_UOM_ID;

Manufacturing

To get the stock it uses the cur_stock cursor of MA_ProductionRun_Standard procedure. This cursor prioritizes stock generated in other production plans of the same work effort. And gets the remaining stock from the m_storage_detail filtering by product, uom, product_uom (optional) and organization of the warehouse. It orders the results by the priority of the locator and the last updated date of the m_storage_detail record.

CURSOR Cur_Stock (v_Product_old VARCHAR, v_UOM_old VARCHAR, v_Product_UOM_old VARCHAR, v_Org_old VARCHAR, v_Production_Id VARCHAR, v_Line NUMBER) IS
  SELECT T.M_PRODUCT_ID, T.C_UOM_ID, T.M_LOCATOR_ID, T.M_ATTRIBUTESETINSTANCE_ID,
         T.M_PRODUCT_UOM_ID, COALESCE(T.QtyOnHand, 0) AS Qty, T.QtyOrderOnHand AS QtyOrder,
         U.STDPRECISION, L.PRIORITYNO, T.UPDATED AS lastupdated
  FROM M_LOCATOR L, M_WAREHOUSE W,
       M_STORAGE_DETAIL T
           LEFT JOIN M_PRODUCT_UOM PU ON T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
           LEFT JOIN C_UOM U ON PU.C_UOM_ID = U.C_UOM_ID
  WHERE T.M_PRODUCT_ID=v_Product_old
    AND T.C_UOM_ID=v_UOM_old
    AND (v_Product_UOM_old IS NULL OR T.M_Product_UOM_id = v_Product_UOM_old)
    AND T.M_LOCATOR_ID=L.M_LOCATOR_ID
    AND L.M_WAREHOUSE_ID = W.M_WAREHOUSE_ID
    AND AD_ISORGINCLUDED(v_Org_old,W.AD_ORG_ID,W.AD_CLIENT_ID)<>-1
    AND COALESCE(T.QtyOnHand, 0)>0
  UNION
  SELECT M_PRODUCTIONLINE.M_PRODUCT_ID, m_productionline.c_uom_id, m_productionline.m_locator_id, m_productionline.m_attributesetinstance_id,
         m_productionline.m_product_uom_id, m_productionline.movementqty AS qty, m_productionline.quantityorder AS qtyorder,
         c_uom.stdprecision, -1 AS priorityno, m_productionline.updated AS lastupdated
  FROM M_PRODUCTIONLINE, M_PRODUCTIONPLAN, c_uom
  WHERE M_PRODUCTIONLINE.M_PRODUCTIONPLAN_ID = M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID
    AND M_PRODUCTIONLINE.C_UOM_ID = C_UOM.C_UOM_ID
    AND M_PRODUCTION_ID = v_Production_Id     
    AND M_PRODUCTIONPLAN.line < v_Line
    AND M_PRODUCTIONLINE.M_PRODUCT_ID = v_product_old
    AND M_PRODUCTIONLINE.C_UOM_ID = v_UOM_Old
    AND (v_Product_UOM_old IS NULL OR M_PRODUCTIONLINE.M_Product_UOM_id = v_Product_UOM_old)
  ORDER BY PRIORITYNO, LASTUPDATED;

Summary

Most of the logic to retrieve stock from the warehouse is in PL / SQL, so the proposed solution shall be in this language.

All logic requires to filter by a product except the Physical Inventory.

Some filters are always present, although they might be optional: Locator and / or warehouse and attribute set instance.

The methods always return all the m_storage_detail that match the filters ordered by priority. Each procedure manages itself the quantity that needs.

Proposed Solution

The development consists on a new table to store stock that has not reached yet to the warehouse that includes a modification on an existing trigger and a new procedure to manage it. And a new procedure to return the available stock prioritized by user defined rules.

M_STOCK_AUX table

Table that contains stock of products that have not yet produced. On a first phase it will only be used by the manufacturing module. When the transaction document is created this table is populated and when the document is processed all this stock shall return to 0. On the case of manufacturing, when the generate standards process is run the table is populated by triggers on the m_productionline table and when the work effort is validated the stock on this table shall go to zero. Manual changes on the product lines, change of attributes, quantities, etc. shall update this table.

Columns:

AD_Table_ID

table id of the transactional document owner of the movement. On the manufacturing case this is the ID of the M_Production table.

Aux_ID

record id of the transactional document owner of the movement. On the manufacturing case this will be the ID of the Work Effort.

M_Storagedetail_ID

link to the storage detail record where the on hand qty will be added.

Quantity

quantity that will be moved to/from the warehouse. Positive stocks means an increment of stock.

M_UPDATE_STOCKAUX procedure

Procedure similar to M_UPDATE_INVENTORY. Procedure to manage the M_STOCK_AUX table. It receipts as input parameters all the warehouse dimensions (product_id, uom, attribute,...) and the table id and record id of the transactional document that generates the stock movement. This function has to get the corresponding M_STORAGEDETAIL_ID based on the given warehouse dimensions. If no storage detail id is found an exception is raised.

M_PRODUCTIONLINE_TRG trigger

Modify existing core's trigger to call the new M_UPDATE_STOCKAUX procedure after the call to M_UPDATE_INVENTORY procedure to manage the M_STOCK_AUX table. It is needed to retrieve the M_PRODUCTION_ID with a query to calculate the Aux_ID. The AD_Table_ID can be hardcoded.

M_TRANSACTION_TRG trigger

Modify existing core's trigger to call the new M_UPDATE_STOCKAUX procedure after the call to M_UPDATE_INVENTORY procedure to manage the M_STOCK_AUX table. This call has to be included in an if clause to be called only in case the M_PRODUCTIONLINE_ID column is not null. As this flow is the only one implementing the M_STOCK_AUX table at the moment. It is needed to retrieve the M_PRODUCTION_ID with a query to calculate the Aux_ID. The AD_Table_ID can be hardcoded.

M_STOCK_PROPOSED table

New table where is stored the proposed stock on each to the M_GET_STOCK procedure. Needed columns are:

AD_PInstance

Identifier of the call to M_GET_STOCK procedure.

Priority

Number of priority to use the proposed stock.

M_Storage_Detail_ID

Storage detail id to get all the needed warehouse dimensions.

Quantity

Quantity available to use.

M_GET_STOCK procedure

This procedure receives a unique AD_PInstance_ID. Additional parameters are stored as AD_PInstance_Para records related to the AD_PInstance_ID. This way it is possible to extend the parameters on an easy manner.

The output is stored in a new temporary table, M_STOCK_PROPOSED.

Parameters supported on first implementation phase:

• Quantity. Optional. If it is not set all available stock is returned.
• Product id. Mandatory
• Product UOM id. Optional
• Locator id. Optional
• Warehouse id. Optional
• UOM id. Optional (shall be the same defined on the m_product table)
• Organization id. Optional. Warehouse has to be in the given organization or a child one.
• Attribute set instance id. Optional
• Unique attribute. Optional default 'No'. This parameter should return only products of the same attribute set instance. It has to check that there is enough stock available.
• ProcessID. Contains the process ID that have called the procedure.

Other possible parameters to implement on further phases:

• Inventory method will be implemented on a second phase. At this moment only FIFO will be supported using the creation date of the Attribute Set Instance as reference.
• Attribute filter. Ability to filter by a unique attribute of an attribute set. For example on an attribute set containing Lot and Color to be able to get product stocked of any lot but same color.
• Priority of stock on the M_STOCK_AUX table.

The process is divided on 2 steps. On the initial one will insert all the records on the M_STOCK_PROPOSED table. The available stock is retrieved from the M_STORAGEDETAIL and M_STOCK_AUX tables. The available quantity is the sum of the storage detail's QtyOnHand and stock aux's quantity columns. This query is filtered based on the parameters. On a second step these records will be prioritized based on a FIFO rule. The quantity included on the M_STOCK_AUX is prioritized over the FIFO rule.

Customize Stock Proposal

From 3.0MP10 it is possible to customize the stock that Warehouse Stock Management proposes.

Thanks to a new parameter in M_GET_STOCK function called ProcessID (which contains the process ID that have called M_GET_STOCK) and using the extension point M_Get_Stock - Finish Process it is possible to do any customization on the stock proposed.

The way to do this is use the M_Get_Stock - Finish Process extension point to modify the data stored in M_STOCK_PROPOSED. In this extension point you can get any parameter used in M_GET_STOCK as ProcessID to customize the result. You can also get some data from your own module. You can do any customization like order stock (FIFO, LIFO), delete some lines, add some lines, etc....

You can see some examples in this modules:

Reservation in Production Run  : The stock proposed in Create Standards process will take into account the stock proposed in others Production Run.

Warehouse Transactions Management : From version 1.1.0. The aim of this module is to manage the warehouse transactions configuring every warehouse avoiding them to participate just in the transactions they really do.