Maximo Open Forum

 View Only

  • 1.  Has anyone developed a good method to detect inventory stock outs?

    Posted 11-04-2020 12:15
    We just finished our fiscal year and with the focus on our MRO inventory I've had the usual request from end users on metrics and reports for inventory. I finally had a couple of sites ask about a way to alert them or report on the number of stock outs the site had in the past week/month. 

    Has anyone found a good way to track when a stock out has occurred?
    #Inventory
    #Reporting

    ------------------------------
    Jason Verly
    @MyGeekDaddy
    ------------------------------


  • 2.  RE: Has anyone developed a good method to detect inventory stock outs?

    Posted 11-04-2020 13:56
    Hi Jason.

    If I get your point, you could find a solution by tracking the curbal field on inventory transactions table. For instance:
    • You could create an automation script to save the related data and show it on start center or create a KPI.
    • Other option would be creating an scalability to alert the user when stock out happens. 
    If I definitely didn't get your point, please, give me more details.


    ------------------------------
    Daniel Torrescusa
    Piensa en nube
    ------------------------------

    Original Message


  • 3.  RE: Has anyone developed a good method to detect inventory stock outs?

    Posted 11-05-2020 09:11
    This one is difficult for sure. Here's how we've tackled it in the past:

    Our definition of Stock Out is when an item is requested from a storeroom, but the full quantity requested is not available/in stock in the storeroom. Your definition may be slightly different (e.g. the "full quantity" part may not apply).

    To track these scenarios, we created a KPI that compared information in the INVRESERVE table with related information in the INVBALANCES table. The tricky part is also looking at the REQUIREDDATE on the reservation vs the current system date/time, since you can reserve items well in advance of actually needing them. Some folks might consider comparing against the Work Order Scheduled Start Date as opposed to the Required Date of the reservation.

    To add to the difficulty, you also have to consider how often that KPI runs. If you run the KPI daily, will the same reservation be counted each day that the balance is not satisfied? Or should it only be counted once?

    In general, a query like this can get you started (SQL Server syntax):
    select count(1)
    from invreserve r
    inner join invbalances b on r.itemnum = b.itemnum and r.location = b.location and r.siteid = b.siteid and r.itemsetid = b.itemsetid and r.binnum = b.binnum
    where r.reservedqty > b.curbal and r.requireddate < getdate()

    The above query does not consider things like different Item Condition Codes, items in the same storeroom across multiple bins, lot expiration dates, pending or staged items, and a few other conditions. Hopefully it's an OK start though.

    Hope this helps.

    ------------------------------
    Alex Walter
    A3J Group LLC
    ------------------------------

    Original Message


  • 4.  RE: Has anyone developed a good method to detect inventory stock outs?

    Posted 11-05-2020 09:32
    We send a daily morning report to our warehouse---we use this script for the report---we also show the POs associated so they can see if action has been taken on the item:

    SELECT
    inventory.itemnum
    ,(SELECT item.description from dbo.item item WHERE item.itemnum = inventory.itemnum) description
    ,inventory.category
    ,inventory.reorder
    ,inventory.status invstatus
    ,inventory.binnum
    ,0 AS curbal
    ,poline.ponum
    ,poline.receiptscomplete
    ,poline.orderqty
    ,poline.enterdate
    ,poline.status
    FROM dbo.inventory inventory
    LEFT OUTER JOIN (SELECT
    poline.itemnum, poline.ponum, poline.receiptscomplete, poline.orderqty, poline.enterdate, poline.itemsetid, poline.siteid, poline.revisionnum,
    po.status
    FROM dbo.poline poline INNER JOIN dbo.po po ON (poline.ponum = po.ponum AND poline.revisionnum = po.revisionnum AND poline.siteid = po.siteid)
    WHERE poline.receiptscomplete = 0 AND po.historyflag = 0 AND (NOT (po.status IN ('PNDREV','REVISD')))) poline ON (poline.itemnum = inventory.itemnum)
    WHERE
    inventory.status = 'ACTIVE'
    AND inventory.location = 'yourstoreroom'
    -- item is a stocked and reorded item
    AND inventory.category = 'STK' AND inventory.reorder = 1

    ------------------------------
    Julia Scott
    Sarasota County Government
    ------------------------------

    Original Message


  • 5.  RE: Has anyone developed a good method to detect inventory stock outs?

    Posted 11-05-2020 13:44

    Hi Jason,

    I am approaching your question with a proactive data procedure, rather than a reactive query procedure for this question.

    Knowing Lead time and evaluating Reorder points consistently are the basis for reducing stock-out problems.  By evaluating the vendor performance (more than once a year!!!) and consistently running your ROP report, Maximo will keep stock-outs to a minimum. 

    Maximo is sophisticated in its algorithms for calculating ROP.  With data updated on the item record for lead time, the ROP report will look at issue history, cost, and receipt history to help determine the appropriate reorder point. 

    Use Case:

    Required date for item is Nov 1.  PO was issued to the Vendor.  Receipt date is Nov 7.  The vendor missed the required date by 7 days.

    Impact:

                   Work order schedule is pushed, and costs are associated to that work stoppage/rescheduling.

                   No faith in inventory from technicians because are parts are not available when needed

     

    By running the ROP report on a consistent basis, and keeping lead time values current using the vendor analysis, Maximo can help determine a reorder point that becomes useful and valid to reduce stock outs.

    One must take PM generated reservations into account for this scenario, also.  If there are planned materials needed by target start date (equal to required date for items on a PM-generated work order), then you may also have to adjust lead time on the PM for consideration of the vendor-supplied items as well, to make sure you have that item when needed.  An additional consideration is how far in advance you generate PM work orders. If the lead time on the item is zero,  and the lead time on the PM is also zero, then the item will order (assuming it's not in stock) the day it's needed based on target start/required date because both lead times are zero.

    Remember, Maximo's underlying methodology of inventory management is based on Just-In-time inventory.

    Hope this helps!

    Thanks!



    ------------------------------
    Gina Leonard
    Aquitas Solutions
    ------------------------------

    Original Message


  • 6.  RE: Has anyone developed a good method to detect inventory stock outs?

    Posted 11-06-2020 09:15
    Jason,

    Like many, I start by wanting to know your definition of stock out first? Any active item where balance hits zero? Only items reserved where quantity (full or partial) not available in the time period needed for work execution? Maybe you want to see various definitions and scenario's? 

    We deliver this in various ways for various clients, and have some visuals we could share. Maybe I should send to you, or share here, or maybe we should do a coffee chat on this?

    ------------------------------
    Joe Lonjin
    Cohesive Solutions
    ------------------------------

    Original Message


Related Content