on 08-21-2015 11:16 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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'
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
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
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.