Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

-- Item Usage in Item Master Data Ver 1 MJ 2011 12 08

-- Assgin a Formatted search to one of the Fields of your choice.

-- Description: This query will list all the Invoices and Prodcution orders where that item has been
-- used for the itemCode that is currently loaded in the Item Master Data Screen.

-- Authors: Guy Plante & Michael Jenkins

SELECT

T0.CardName, T1.ItemCode, T0.DocDate, T3.FirmName,T1.Dscription, T1.Quantity,T4.[OnHand] as'OnHand',

T4.MinStock, T4.MaxStock, T4.WhsCode

FROM OINV  T0 INNER JOIN INV1  T1 ON T0.DocEntry = T1.DocEntry
            INNER JOIN OITM  T2 ON T1.ItemCode = T2.ItemCode
            INNER JOIN OMRC  T3 ON T2.FirmCode = T3.FirmCode
            INNER JOIN OITW T4 ON T2.ItemCode = T4.ItemCode AND T1.WhsCode = T4.WhsCode

WHERE
 
T1.ItemCode =$[OITM.ItemCode] AND  T0.DocType = 'I'

UNION

SELECT  '(((Production Order)))', T1.ItemCode, T0.PostDate, T3.FirmName,

T2.ItemName,T1.IssuedQty,T4.[OnHand] as'OnHand',  T4.MinStock, T4.MaxStock, T4.WhsCode

FROM OWOR  T0

INNER JOIN WOR1  T1 ON T0.DocEntry = T1.DocEntry
            INNER JOIN OITM  T2 ON T1.ItemCode = T2.ItemCode
            INNER JOIN OMRC  T3 ON T2.FirmCode = T3.FirmCode
            INNER JOIN OITW T4 ON T2.ItemCode = T4.ItemCode AND T1.wareHouse = T4.WhsCode

WHERE

T1.ItemCode =$[OITM.ItemCode]    AND T1.IssuedQty > 0

UNION

SELECT 'End of report','', '2001-01-01 00:00:00.000','', '',0,0,0,0,''

UNION

SELECT 'Beginning of report','', '2099-12-31 00:00:00.000','', '',0,0,0,0,''

order by
3 desc

  • No labels