PO line detail on General Ledger table (LedgerTrans)

Dynamics 365 for Finance and Operations/AX General discussions
#1

Hi

I drive the majority of my reporting using ODBC to extract data from SQL. I am really struggling however to get line item detail from purchase orders onto the my general ledger transactions, when a delivery note (picking list) is posted.

Has anyone experienced this / knows how to link this data?

Many thanks

Ade

#2

So you have a field ItemId on the ledgerTrans and you want to update it from purchase order line? If so why cannot use the financial dimensions for that purpose? If that is not the requirement, please elaborate.

#3

Thanks for the reply

So say I have a packing slip transaction for eg below. What I need is the detail / text from the line that this packing slip related to on the purchase order! I’ve tried trimming the last 8 digits from the text but this obviously only gives me a link to the PO header…

Voucher = INR002488 Text = Invoice 043-277542; AP 2100473; PO 46020063

#4

So you trying to retrieve data from AX? How you are getting that text?

To go into PO line you need to know the InventTransId of that line.

#5

Hi - I’m getting that straight out of the LedgerTrans table, using ODBC to rip it directly out of SQL. The trouble is that the LedgerTrans table doesn’t hold the InventTransID. Do you know how I can get that relationship. The accountants like to be able to drill down on a specific GL code to see what makes up their spend for eg

#6

There could be multiple items per single voucher.
You can look into vendPackingSlipJour by using from there you can get vendPackingSlipTrans (this has inventTransId)
select vendPackingSlipJour
where vendPackingSlipJour.LedgerVoucher == ledgerTrans.Voucher &&
vendPackingSlipJour.DeliveryDate == ledgerTrans.TransDate
(or) you can directly look into inventTrans and get inventTransId’s
select inventTrans
where inventTrans.VoucherPhysical == ledgerTrans.Voucher &&
inventTrans.DatePhysical== ledgerTrans.TransDate

#7

I will give that a try and let you know if it works.

Thank you very much for your help, it is very much appreciated