cancel
Showing results for 
Search instead for 
Did you mean: 

Serial No Management Query

Former Member
0 Kudos

Hi,

I am trying to make a query report. My requirement is to generate list of all open Goods Return with its Item Serial NO.

I have tried to make the same with the below mentioned query but instead of throwing one single result its throwing repeated list.

Can any body tell me what relation I can get between between RPD1 and OSRN OR OSRI.

SELECT T2.DocNum,T2.CardCode,T2.CardName,T2.NumAtCard,T1.IntrSerial, T2.DocDueDate, T2.DocTotal,T2.DocStatus FROM SRI1 T0 

INNER JOIN OSRI T1 ON T0.[SysSerial] = T1.[SysSerial]

INNER JOIN ORPD T2 ON T2.OBJTYPE=T0.BASETYPE 

AND T2.DOCNUM=T0.BASENUM

INNER JOIN RPD1 T3 ON T2.[DocEntry] = T3.[DocEntry]

WHERE T0.[BaseType] =21

Where T2.DocNum = '300181'

Thanks and Regards

Chandan Lal

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Pradeep,

Your Query works fine in my DEMO DB. Number of lines in your query result is depends on the Qty in Goods Return documents. Ex: If your Goods return Qty is 5 then your query result will contain 5 rows since you included Serial No in your select statement.

Former Member
0 Kudos

Hi Bharathiraja,

Thanks for your reply make a note my version of SAP B1 is 9.1 PL07. Secondly I agree with you that it will repeat if it will have more than one quantity as I have mentioned serial No. But the DocNum I am passing contains only one row  and one quantity,And my query result is showing all the Serial No which is being transacted for the selected item.

Former Member
0 Kudos

Hi Pradeep,

Try this query

SELECT  Distinct T2.DocNum,T2.CardCode,T2.CardName,T2.NumAtCard,T1.IntrSerial, T2.DocDueDate, T2.DocTotal,T2.DocStatus FROM SRI1 T0

INNER JOIN OSRI T1 ON T0.[SysSerial] = T1.[SysSerial]

INNER JOIN ORPD T2 ON T2.OBJTYPE=T0.BASETYPE

AND T2.DocEntry=T0.BaseEntry

INNER JOIN RPD1 T3 ON T2.[DocEntry] = T3.[DocEntry]

WHERE T0.[BaseType] =21

Where T2.DocNum = '300181'

Former Member
0 Kudos

Hi Bharathiraja,

I found solution , below is the query,

Select T0.DocNum,T2.CardCode,T2.CardName,T0.NumAtCard, T2.DistNumber, T0.DocDueDate, T0.DocTotal,T0.DocStatus from ORPD T0

LEFT OUTER JOIN SRI1_SN T2 ON T2.DocNum = T0.DocNum

WHERE T0.DocStatus = 'O'

Thanks for your assistance but Distinct will not solve the purpose

Thanks & Regards,

Pradeep