Finance | Project Operations, Human Resources, ...

the duplicated ines when linking Inventtransposting and Ledgertrans tables

(0) Share

Report

Posted on by mangosteenlu

Dear All,

I wonder if anyone on this forum can enlighten me with your knowledge/thoughts please. I am trying to built a report to see how much stock write offs on all stock items each month. To capture the right data, I need to link ledgertrans with inventtransposting table to show stock item ID and how much cost being posted to our general ledger codes (stock w/offs --- there are several being set up in AX). I know the relationship between two tables is voucher number, however there are duplicated lines (please see the examples below). Can someone tell me how to get rid of the duplicates please? thanks

select P.ITEMID,P.TRANSDATE,P.VOUCHER,T.AMOUNTMST,P.ACCOUNTOFFSET,P.ACCOUNT from INVENTTRANSPOSTING P
join LEDGERTRANS T
on P.VOUCHER=T.VOUCHER
where P.TRANSDATE= '2022-03-01' and P.ACCOUNTOFFSET like '2%'

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

André Arnaud de Cal... 305,178 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi mangosteenlu,

There are records in the InventTransPosting table for both the physical as well as the financial update. When you post a write off via an inventory journal, for the same voucher there is a record for both. You need to filter on the type to get only the records related to the financial updates.

Was this reply helpful? Yes No
mangosteenlu 19 on at

Like (0)

Report
Copy link

Link copied!

Hi Andre,

Thank you very much for reply. I cannot find any value for transactions in Inventtransposting table. (please see the below screenshot) I've filtered voucher by journal number starting with 'J' but I need to link to other table to show the value of stock journal. Can you please advice? thanks

Was this reply helpful? Yes No
André Arnaud de Cal... 305,178 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi mangosteenlu,

Can you elaborate? I'm not able to understand your reply. Is this a new follow up question? What do you mean with 'other table'? What exactly have you now shared as screenshot?

Was this reply helpful? Yes No
mangosteenlu 19 on at

Like (0)

Report
Copy link

Link copied!

Hi André,

At each month end, we are trying to identify how much stock items being written offs (stock items, Qty, and value). Therefore I need to link Inventransposting and Legertrans table together to get the information I need.

All stock items with stock journal numbers are listed on the Inventtransposting table but no costs attached to them, in order to find the costs for these stock w/offs, I find there is a relationship between the Inventtransposting and Ledgertrans table (Inventtransposting. voucher=Ledgertrans.voucher) however there are duplicated lines when i execute my SQL query. Do you know any better way to do so?

Thank you so much for your help.

Was this reply helpful? Yes No
André Arnaud de Cal... 305,178 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Mangosteenlu,

To get the cost, you can also link the InventTrans record which contains the quantity and the cost amount. This table has a direct link based on the InventTransId, (financial) Voucher and (financial) TransDate

Was this reply helpful? Yes No