Challenge linking to Landed Costs?

[Former Member]

Hi Everyone,

I am writing a report (query) to show information related to the purchase of goods.

Furthermore my query is intended to show information related to a Purchase Order > Goods Receipt Purchase Order(s) > Landed Cost(s).

Below is a sample of a partial relationship map related to the query that I am attempting to write -

(red ticks represent the data that I need to retrieve)

The query that I have written (shown next) can already retrieve information related to the Purchase Order(s) and Goods Receipt Purchase Order(s) related to an Item. However I am at a loss concerning how to join in the Landed Cost tables (OIPF & IPF1)

Here is the query that I have written so far -

DECLARE @itemCode nvarchar(30), @choice int

SET @itemCode = 'xxx-xxx'

SELECT
T1.DocNum AS 'Purchase Order #'
, T1.DocDate AS 'PO Date'
, T0.Quantity AS 'PO Qty'
, T3.DocNum AS 'GRPO #'
, T3.DocDate AS 'GRPO Date'
, T2.Quantity AS 'GRPO Qty'

FROM POR1 T0
INNER JOIN OPOR T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN PDN1 T2 ON T2.DocEntry = T0.TrgetEntry AND T2.ItemCode = @itemCode
INNER JOIN OPDN T3 ON T3.DocEntry = T2.DocEntry

WHERE T0.ItemCode = @itemCode

I have attempted to link to the Landed Costs tables like so;

LEFT JOIN IPF1 T4 ON T4.DocEntry = T2.BaseEntry AND T4.ItemCode = @itemCode
LEFT JOIN OIPF T5 ON T5.DocEntry = T4.DocEntry

However if I SELECT T5.DocNum all I get returned are NULLs.

Any suggestions on how to link to the Landed Costs tables will be greatly appreciated.

Kind Regards,

David

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription],
T1.[Quantity],T1.[BaseEntry],t2.docentry,T1.[LineNum],T2.[BaseLine],
T1.[Reference] FROM OIPF T0 INNER JOIN IPF1 T1 ON T0.DocEntry =
T1.DocEntry left join PDN1 T2 on T1.[BaseEntry] = t2.docentry and
T1.[LineNum] = T2.[BaseLine] INNER JOIN OPDN T3 ON T2.DocEntry =
T3.DocEntry

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

Thank you for your response. I am wondering if it is possible to go 'the other way', which is to say to work from the Purchase Order to the GRPO to the Landed Costs, rather than from the Landed Costs backwards?

I ask because I really need to show all items that are on order (currently) but which have not yet been received, as well as the items that have already been received (i.e.: have landed costs associated).

Any further help will be greatly appreciated.

Kind Regards,

David

[kothandaraman_nagarajan · Accepted solution]

Hi,

Add required field from basic query:

SELECT T0.[DocNum], T3.[DocNum], T5.[DocNum] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry left join  PDN1 T2 on T2.[BaseEntry]  = T1.Docentry and  T2.[BaseLine]  = T1.[LineNum] left JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry left join  IPF1 T4 on T4.[BaseEntry] = T2.Docentry and T4.[LineNum]  = T2.[BaseLine] left JOIN OIPF T5 ON T3.ImportEnt = T5.DocEntry

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thank you