Adding Purchase Orders info to report (crystal)

175 Views

I'm writing a report that includes sales order info + delivery notes. What I'm trying to do is add information from the purchase orders table (I believe it is OPOR). How do I link this table to one or more of the tables I already have? Please see my query below:

SELECT o0.NumAtCard AS OrderNo
,i0.DocNum
,i0.TrackNo AS TrackingNo
,i0.CardName
, o0.DocNum AS DelivDoc
FROM ODLN d0
INNER JOIN DLN1 d1 ON d0.DocEntry = d1.DocEntry
LEFT OUTER JOIN INV1 i1 ON d1.TrgetEntry = i1.DocEntry
LEFT OUTER JOIN OINV i0 ON i1.DocEntry = i0.DocEntry
INNER JOIN ORDR o0 ON d1.BaseEntry = o0.DocEntry
INNER JOIN OITM itm ON i1.ItemCode = itm.ItemCode
WHERE o0.SlpCode = 18 --website AND
o0.CANCELED = 'N' AND
i0.TrackNo IS NOT NULL
GROUP BY o0.NumAtCard
|,i0.DocNum
|,i0.TrackNo
,i0.CardName
,o0.DocNum
ORDER BY o0.NumAtCard