Joining JDT1 + OPCH and then OPCH + PCH1

[Former Member]

Hi,

I am looking for a way to link the tables as shown above in the title.

I am trying to create a report whereby I can search an account code from OACT, and in return I want to display all of the documents along with their descriptions.

I've got to a stage where I can search the account code and display all of the transactions for the acct code. I am looking for a way to breakdown each of the entries to display the invoice descriptions row by row.

I apologise if my description is a little confusing, I am not the best at explaining things!

TL;DR - Instead of showing just the 'AP invoice - [supplier code] in the details columns of G/L Account, I want a breakdown of what's actually on the invoice.

[former_member185682 · Accepted solution]

Hi Joshua,

Maybe I don't understand well what you want achieve, but JDT1 is not connected with OPCH. You can join ODJT with OPCH and then OPCH with PCH1.

SELECT
	*
FROM
	OJDT T0
	INNER JOIN OPCH T1 ON T0.TransId = T1.TransId AND T0.TransType = 18
	INNER JOIN PCH1 T2 ON T2.DocEntry = T1.DocEntry

Hope it helps.

Kind Regards,

Diego Lother