on 10-16-2008 7:12 AM
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
I did something very similar with ORDR to find a the serial numbers that were related to a sales order. Maybe this will help you:
SELECT distinct osri.IntrSerial FROM osri
JOIN sri1 ON dbo.SRI1.SysSerial = dbo.OSRI.SysSerial AND dbo.OSRI.ItemCode = sri1.ItemCode
JOIN odln ON dbo.odln.DocNum = dbo.SRI1.BaseNum
JOIN dbo.DLN1 ON odln.DocEntry = dln1.DocEntry
JOIN ordr ON dln1.BaseRef = ordr.DocNum
WHERE ordr.DocNum = [%0]
AND dbo.SRI1.CardCode = ordr.CardCode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ramesh,
In your business process do you copy the Sales Order to the AR Invoice or do you have a delivery step in between.
If you have the delivery step then all links are with the Delivery document and not the AR Invoice.
I can surely help you with this once I hear back
NOTE: The link between the Delivery or Invoice is in the SRI1 table and not OSR1. Entried in OSRI are created only when the Items are received.
Suda
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Add Distinct to the first line between Select and T2.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.