I am linking from the Whse Journal table (OIVL) to A/P Reserve Invoices (OPCH). Then I am attempting to link to Goods Receipt Purchase Orders (OPDN) from 'A/P Reserve Invoices', however unfortunately so far I have only part succeeded.
Below is a small section of a Relationship Map which shows the A/P Reserve Invoice and the Goods Receipt PO.
So far I have succeeded in linking to the header of the Goods Receipt PO from the A/P Reserve Invoice, however I have not been able to link directly to line level items in the Goods Receipt P/O.
Here is the code that I have written so far -
DECLARE @itemCode nvarchar(30), @choice int
SET @itemCode = 'EG615.402'
CAST(T6.DocNum AS nvarchar) AS 'Purchase Order #'
, CAST(T1.DocNum AS nvarchar) AS 'A/P Reserve Inv #'
, ISNULL(CAST(T4.DocNum AS nvarchar), '') AS 'Goods Receipt PO #'
, CONVERT(nvarchar, T4.DocDate, 103) AS 'GRPO Date'
, CAST(DATEDIFF(DD, T6.DocDate, T4.DocDate) AS nvarchar) AS 'Lead Time'
, CAST(T2.Price AS decimal(10,2)) AS 'Buy Price'
, CAST(CAST(T2.Rate AS decimal(10,2)) AS nvarchar) AS 'Ex.Rate'
, '' AS 'Landed Cost'
, '' AS 'Import %'
, '' AS 'Duty Rate'
, ISNULL(T5.U_INE_FreightType, '') AS 'Freight Type'
, ISNULL(T5.U_A1WMS_Comments, '') AS 'Warehouse Comments'
FROM OIVL T0
INNER JOIN OPCH T1 ON T1.DocEntry = T0.CreatedBy
INNER JOIN PCH1 T2 ON T2.DocEntry = T1.DocEntry AND T2.LineNum = T0.DocLineNum
LEFT JOIN PDN1 T3 ON T3.DocEntry = T2.TrgetEntry AND T3.LineNum = ?
LEFT JOIN OPDN T4 ON T4.DocEntry = T3.DocEntry
LEFT JOIN POR1 T5 ON T5.DocEntry = T2.BaseEntry AND T5.LineNum = T2.BaseLine
LEFT JOIN OPOR T6 ON T6.DocEntry = T5.DocEntry
WHERE T0.ItemCode = @itemCode
AND T0.TransType = '18'
AND T1.isIns = 'Y' -- Reserve Invoices Only
Notice above that the ? represents the section of this query which I am stuck on!
If anybody can offer any suggestions here they will be greatly appreciated.