You’re offline. This is a read only version of the page.
Announcements
19
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%'
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
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.
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?
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
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.
#1
André Arnaud de Cal...
294,217
Super User 2025 Season 1
#2
Martin Dráb
232,978
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
