Skip to Content
0
Former Member
Dec 07, 2011 at 07:18 PM

need help in query to have all items in sap with PO data on order, date

42 Views

Hello Everyone,

I need some help in writing a query to show all items in sap db to have itemcode, itemname, onhand, sellable(onhand-committed), on order, and the delivery date when that time should be received. I need to know what items are regular items, which ones are master and components. For the master I need to have the deliver date of the PO for the component.

I have written this.

SELECT DISTINCT TOP (100) PERCENT T1.ItemCode, SUM(T1.Sellable) AS Sellable, T1.OnOrder, MAX(T1.Docduedate) AS DeliveryDate, T1.WhsCode

FROM (SELECT ItemCode, Sellable, OnOrder, '1/1/1900' AS Docduedate, WhsCode

FROM dbo.V_RAZ_StoreInv

UNION ALL

SELECT TOP (100) PERCENT ItemCode, '0' AS Sellable, Quantity AS Onorder, DocDueDate, WhsCode

FROM dbo.V_RAZ_OPENPOSDATA

ORDER BY ItemCode) AS T1 LEFT OUTER JOIN

dbo.V_RAZ_ItemInfo ON T1.ItemCode = dbo.V_RAZ_ItemInfo.ItemCode

GROUP BY T1.ItemCode, T1.OnOrder, T1.WhsCode, dbo.V_RAZ_ItemInfo.OnHold

HAVING (dbo.V_RAZ_ItemInfo.OnHold = 'n')

ORDER BY T1.ItemCode, T1.WhsCode

and for the v_raz_openposdata I have the following query

SELECT dbo.OPOR.DocNum, dbo.OPOR.DocStatus, dbo.POR1.WhsCode, dbo.POR1.ItemCode, dbo.POR1.Dscription, dbo.POR1.Quantity, dbo.POR1.OpenQty,

dbo.POR1.LineStatus, dbo.OPOR.DocDueDate

FROM dbo.OPOR LEFT OUTER JOIN

dbo.POR1 ON dbo.OPOR.DocEntry = dbo.POR1.DocEntry

WHERE (dbo.OPOR.DocStatus = 'O')

Any help is greatly appreciated.

Right now I do get the delivery date for regular items and components but the master sku for the component all of them have 1/1/1900.

Thank you