Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How do I get Serial Number of and Item in invoice (INV1 and OSRI tables)

I am new to SAP development, bear with me if I make any mistake.

I am trying to get the serial number of an Item on an Invoice. The code I am using is

select T2.DocNum, T3.ItemCode as 'Item Number', s1.intrSerial as 'Serial Number',

T3.Dscription as description, T3.vendorNum as 'Vendor Model No',

T3.Quantity as 'Qty Sold',

T3.whscode as 'From Warehouse', lastpurprc as 'Last Purchase price',

O2.Price as 'MSRP', T3.Price as 'Sold price',

inDate as 'Date First Recd', convert(varchar(10),T2.DocDate,101) as 'Date Sold'

FROM OINV T2, INV1 T3,

OITM O1, ITM1 O2,OSRI S1

where T3.ItemCode = O1.ItemCode

and O1.ItemCode = O2.ItemCode

and T2.DocEntry = T3.DocEntry

and T2.DocNum ='2201'

and O2.pricelist =1

and O1.ItemCode = S1.ItemCode

and O2.ItemCode = S1.ItemCode

and S1.whscode=T3.whscode

and convert(varchar(10),T2.DocDate,120) = '2008-09-01'

The problem is that I am getting 9 rows instead if 1 because there is no one-to-one relation to OSRI table which keeps the SerialNumber.

Please guide me

Thanks

Ramesh

Edited by: Ramesh Ramchandani on Oct 15, 2008 9:14 PM

Former Member
Former Member replied

Hi try using this query:

select

T2.DocEntry

, T0.ItemCode

, T0.SysSerial

, T0.IntrSerial as 'Serial Number'

, T1.BaseType

from OSRI T0 join SRI1 T1 on T0.ItemCode = T1.ItemCode and T0.SysSerial = T1.SysSerial

join INV1 T2 on T2.DocEntry = T1.BaseNum and T2.Itemcode = T0.ItemCode

where T1.BaseType = '13' -- 13 is for Sales Invoices

order by T2.DocEntry, T0.ItemCode

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question