Mesclar dados OJDT JDT1 com a AJDT - Merge OJDT JDT1 data with AJDT

[Ederson]

Olá,

Estou enfrentando uma demanda que envolve a mesclagem da consulta do saldo do plano de contas, que inclui os lançamentos contábeis das tabelas OJDT e JDT1, com a tabela de log de modificações AJDT, que é acessada dentro do lançamento contábil. No entanto, não estou conseguindo identificar as chaves relacionadas entre as tabelas OJDT e JDT1 com a AJDT. Tentei pela TransId mas os números não batem

Alguém já criou uma consulta semelhante ou pode me ajudar com isso?

English

Hello,

I am facing a demand that involves merging the account balance query, which includes the accounting entries from the OJDT and JDT1 tables, with the modification log table AJDT, which is accessed within the accounting entry. However, I am unable to identify the related keys between the OJDT and JDT1 tables and the AJDT. I tried using the TransId, but the numbers don't match.

Has anyone created a similar query or can anyone help me with this?

 

 

[LoHa · Accepted solution]

Hi Ederson,

the relation can b found here

https://biuan.com/?q=ojdt and https://biuan.com/?q=jdt1 

Try to find a TrnsId with more lines in AJDT

SELECT TransId 
FROM 
AJDT
GROUP BY TransId
HAVING Count(TransId) > 2

And then have a look on the lines

DECLARE @TransId as INT
SET @TransId = 807944
;
with DataJDT AS
(
SELECT TOP 20 * FROM AJDT WHERE TransId = @TransId
UNION ALL
SELECT TOP 20 * FROM OJDT WHERE TransId = @TransId
)
SELECT TransId,UpdateDate,LogInstanc,DataVers,* FROM DataJDT

regards

Lothar

[Ederson · Accepted solution]

Hello Loha.

Thank you very much for your attention.

I did the tests here and it worked, I will use the query you created to build mine.

Again, thank you very much.

[Ederson · Accepted solution]

And thank you for the website recommendation

I was basing myself on sap.erpref which seems to be outdated.