ATP-Query für same Results as SAP-function Available-to-promise

[Former Member]

Good Morning Experts,

Can you help me, i want create a query, which gives the sames results.
actual Stock, Incomming, Outgoing, order by Date

Thanks for your help.

[Johan_H · Accepted solution]

Hi Markus,

There is a simple solution:

SELECT (OnHand - IsCommited + OnOrder) AS ATP FROM OITM

However if you need to take dates into account (when will new stock arrive, etc) or other logic (for example: first come first serve), then it gets more complicated, and you would have to describe in more detail what you need

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

thanks for your answer.

Yes I need a Query which gives me all positions from

Onhand from a Stock

IsCommited with outgoing dates

OnOrder with Incomming dates

-> same list as ATP

Is this possible ?

[Johan_H · Accepted solution]

Hi Markus,

It is certainly possible, but because it is also quite complicated, it would take a lot of effort and time.

Why not use the ATP list ?

Regards,

Johan

[kothandaraman_nagarajan · Accepted solution]

Hi,

Have you checked Inventory status report under Inventory reports?

Thank you.

[Former Member · Accepted solution]

Hi Johan, Hi Nagarajan,

I need this query for our Sales-Dept. because we have a Database für Sales and a Database for Production, so they can Start Query in Sales-Database, and the Calculations/Results are comming from Production-Database. (Sales Database doesn't have an own Stock)

Next time, I try to make some Queries with a UNION ALL

1. STOCK (Onhand)

2. Incomming from Purchase

3. Outgoing for Sales

4. Incomming from Production

5. Outgoing for Production (If Child-Item can be Sold, too)

If I have a query ready, I'll share it.

Thanks

[Johan_H · Accepted solution]

Hi Markus,

Ok, please let us know if we can help you.

A few tips for developing this query:

• use MS SQL Management Studio, it is easier
• to get incoming and outgoing quantities for all items, use LEFT OUTER JOIN
• to get incoming and outgoing quantities by date, you can use loops and concatenation to build the query text. Then use EXECUTE (YourQueryText) to execute the query. Also please take a look at the answer by Bharathiraja Jin this post. It is a very nice example that you might be able to adapt to your needs.

Regards,

Johan

[Former Member]

Hi again.

here is my first Query, but there I haven't a calculation of the Availible-Quantity...

Can you help me to get this, same as ATP-Function ?

I need this query for FMS...

SELECT 'Stock 01' AS Type,'' AS Source,   



'0' as 'Outgoing', '0' as 'Incomming',  



T0.onHand AS SALDO, '2000-01-01 00:00:000' AS Date,   



'' as 'Customer', '' as 'INTERN'    



FROM OITW T0  



WHERE T0.ItemCode = '$[OITM.ItemCode]' AND T0.WhsCode IN ('01')    



UNION ALL    



SELECT CASE WHEN T0.Status = 'P' THEN 'Incomming from Prod - planned' ELSE 'Incomming from Prod' END AS Type ,T0.DocNum AS Source,  



'0', CASE WHEN (T0.PlannedQty - T0.CmpltQty) < '0' THEN '0' ELSE (T0.PlannedQty - T0.CmpltQty)END ,   



CASE WHEN (T0.PlannedQty - T0.CmpltQty) < '0' THEN '0' ELSE (T0.PlannedQty - T0.CmpltQty)END AS SALDO, CONVERT(DATETIME,T0.DueDate, 104) AS Date, '', 'Stock'  



FROM OWOR T0    



WHERE T0.ItemCode = '$[OITM.ItemCode]' AND T0.Status NOT IN ('L','C') and T0.Warehouse = '01'  



UNION ALL    



SELECT 'Eingang aus Einkauf'AS Type,T0.DocNum AS Source,   



'0', T1.OpenQty,   



T1.OpenQty AS SALDO, CONVERT(DATETIME,T1.ShipDate, 104) AS Date, '', 'Stock'  



FROM OPOR T0    



INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry    



WHERE T1.ItemCode = '$[OITM.ItemCode]' AND T1.LineStatus = 'O'    



UNION ALL    



SELECT 'Outgoing for Production' AS Type, T0.DocNum AS Source,   



(T1.PlannedQty - T1.IssuedQty)*-1, '0',   



(T1.PlannedQty - T1.IssuedQty)*-1 AS SALDO, Date =    



CASE 'Y'   



WHEN 'Y' THEN CONVERT(DATETIME,T1.U_BXPDueDt, 104)    



ELSE    



CASE ISNull(T2.LeadTime,0)    



WHEN 0 THEN CONVERT(DATETIME,T0.DueDate, 104)    



ELSE DATEADD(day,T2.LeadTime,CONVERT(DATETIME,T0.DueDate, 104))    



END    



END, '', 'Production'
 



FROM OWOR T0    



INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry    



INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode    



WHERE T1.ItemCode = '$[OITM.ItemCode]' AND T0.Status NOT IN ('L','C') and T1.wareHouse = '01'  



UNION ALL  



SELECT 'Outgoing Customer' AS Type, T0.U_BaseDocNum AS Source,  



T1.OpenQty * -1, '0',   



T1.OpenQty * -1 AS SALDO, CONVERT(DATETIME,T1.ShipDate, 104) AS Date,  



T0.U_CustCardName, ''  



FROM ORDR T0    



INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry    



WHERE T1.ItemCode = '$[OITM.ItemCode]' AND T1.LineStatus = 'O'  



order by date  

Thank you very much for help.

Markus

[Johan_H]

Hi Markus,

The OITM and OITW tables both have OnHand (actual stock), OnOrder (open purchase order rows), and Commited (open sales order rows) columns.

Usually to get Available, you would simply calculate (OnHand + OnOrder) - Commited.

But because your query includes purchase, sales and production data through UNION statements each with their own filters, I am not entirely sure what "available" means exactly in your scenario.

Regards,

Johan

[Former Member]

Hello Johan,

thanks for your message. I'll try to explain what I mean.

For example:

ATP-Function from SAP:

For each row, I have the availible Qty, suitable to each Delivery-date

Calculation in Excel:

I'm looking for a solution, to calculate the "red" quantities in my Query... dependent from my Warehouse # 01.

Result of Query - without the new column "availible at Date"

I hope, you understand it now better ?

Thx

Markus

[Johan_H]

Hi Markus,

Ok, this will probably need some testing, but you could use an embedded query.

You said you wanted to use this query in an FMS. Do you mean that you need a query result with a single value ?

What I mean is, the parameters are the ItemCode and a date, and your result tells you only what the available saldo is on that date ?

Or do you need the list as in your middle screenshot ?

Regards,

Johan