AR SALES ORDER VS AR INVOICE WITH ITEM DETAILS

[bradderz]

Hi Experts, I have been developing the below query in the aim to pull through a query based on a date range of one month however SAP just seems to crash each time no matter what i change the WHERE parameters too, Please can you advise:

SELECT DISTINCT

T3.[DocNum] AS 'SO Number', T3.[DocDate] AS 'SO Date', T0.[DocNum] AS 'INV Number', T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[SlpCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal],T2.[ItmsGrpNam] FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry], OITB T2, ORDR T3

WHERE T3.[DocDate] >=[%0] and T3.[DocDate] <=[%1]

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

As you're looking to get most out of your community membership, please consider include a profile picture to increase user engagement & additional resources to your reference that can really benefit you:

• Profile https://developers.sap.com/tutorials/community-profile.html
• Tips for Questions: https://community.sap.com/resources/questions-and-answers
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,

Alex

[SonTran]

Hi,

Try this

SELECT 
T3.[DocNum] AS 'SO Number', T3.[DocDate] AS 'SO Date', T0.[DocNum] AS 'INV Number', T0.[DocDate], T0.[CardCode], T0.[CardName], 
T0.[SlpCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal],T5.[ItmsGrpNam] 
FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT JOIN RDR1 T2 on T1.BaseType='17' and T1.BaseEntry=T2.DocEntry and T1.BaseLine=T2.LineNum
LEFT JOIN ORDR T3 on T2.DocEntry=T3.DocEntry
LEFT JOIN OITM T4 on T1.ItemCode=T4.ItemCode
LEFT JOIN OITB T5 on T4.ItmsGrpCod = T5.ItmsGrpCod
WHERE T3.[DocDate] >=[%0] and T3.[DocDate] <=[%1]

The query is based on SO >>> AR process. If SO >>> Delivery >>> AR process, the query need to adjust for linking to DLN1, then RDR1.

Hope this helps,

Son Tran