Skip to Content

ATP Query To Show Negative Stock Quantities

Hi Experts

I need some help in writing a query that will display all components that are in negative in the ATP

Which tables do I need to use?

Thanks

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 19 at 05:57 PM

    Hi Rahul,

    The ATP concept is more from operation's point of view. Available = In Stock + On Order - Committed

    In Stock: If I remember correctly, the OINM has the columns "InQty" and "OutQty", you should be able to get the "In Stock" quantity which shown in the ATP function. However, there was no a "total" column tracking the running total in this table, so you have to run through the whole table to count each move since day 1 when an item came in to get the current total.

    On Order: is the quantity from Open Sales Orders.

    Committed: is the quantity from Open Purchase Orders and Open Work Orders if you have Production module in place.

    Hope this helps.

    Jimmy

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19 at 12:42 AM

    Hi Rahul,

    I don't recall exactly but there should be a report or something under inventory that you can see items with Qty below 0.

    If you prefer a SQL, then you can take a look at the OINM table. Basically it's the warehouse journal tracking each inventory move.

    Jimmy

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 22 at 09:44 AM

    Hi,

    what about using a MRP-Scenario.

    all SalesOrders, all ProductionOrders, all PurchaseOrders without Min/Max-Stock-Levels


    After that you will get a list till your DueDate to check each Item.

    ATP is a question of TimeFrame. You want to see all negative, so you have to define a timeframe in the Scenrio from MRP.


    If you want to make a query, you have to check:

    Salesorders (RDR1) and ProductionOrder-Lines (WOR1/ (OWOR for the duedate)) for Outgoing

    PurchaseOrders (POR1) and ProductionOrders (OWOR) for Incomming

    Stock-Quantities (OITW) in all active warehouses

    the OINM-Table only gives information about previous bockings, not for the future incomming or outgoing quantities

    Hope that helps.

    Markus

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Markus

      Yes we do use MRP, but on a weekly basis

      But what we have been finiding is, sometimes new urgent orders come in and use up some stock, so i just need a query to quickly do a check to see whats falling short

      Thanks

      Rahul