Skip to Content
0
Jun 05, 2020 at 05:31 AM

Service call report in Crystal Reports for SAP B1

626 Views

Hey all,

So im a bit stuck, im in a position where the Links section of database expert doesn't show any tables, thats a question i put in another thread so im working with an alternative.

Ive instead added a command with an SQL query to try and control the joins a bit more and get the information i need.

My goal is to create a maintenance report that can be accessed via each individual service call record in SAP B1, the report needs to show OSCL service call information as well INV1 rows for that particular service call. Additionally it needs to show all the equipment for that customer from the OINS table.

The issue that im having is both the INV1 rows returned and the OINS rows are returning unrelated records, amongst the correct ones. Below is my SQL query that im using, is there any better way to join them to return the information?

SELECT T1.callID, T1.BPBillAddr, T1.custmrName,T1.NumAtCard, T1.closeDate, T1.resolution,T3.ItemCode,T3.Dscription,T4.itemCode,T4.itemName
FROM OCRD T0
LEFT OUTER JOIN OSCL T1 ON T1.customer = T0.CardCode
LEFT OUTER JOIN OINV T2 ON T2.CardCode = T0.CardCode
LEFT OUTER JOIN INV1 T3 ON T3.DocEntry = T2.DocEntry
LEFT OUTER JOIN OINS T4 ON T4.customer = T0.CardCode