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