Skip to Content
0

ATP Query To Show Negative Stock Quantities

Jan 18 at 01:07 AM

85

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Jimmy Liang Jan 19 at 05:57 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jimmy Liang Jan 19 at 12:42 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Jimmy

Thanks for the reply

I tried the OINM table without much luck

Regards

Rahul

0
Markus Schaefer Jan 22 at 09:44 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0