Skip to Content
0
Former Member
Feb 15, 2015 at 11:45 PM

Challenge linking from 'A/P Reserve Invoice' to 'Goods Receipt PO'?

239 Views

Hi Everyone,

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'

SELECT

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'

, T3.Quantity

, 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'

, T6.CardCode

, T6.CardName

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.

Kind Regards,

David

Attachments

Capture.JPG (32.9 kB)