SQL query for Vendor transactions

(0) Share

Report

Posted on by Community Member

Does anyone have SQL written to pull vendor transactions from Dynamics AX which have been posted to the GL? I am just learning and have pulled vendor name and amount but need to figure out the criteria for posted.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Mea_ 60,284 on at

Like (0)

Report

Hi avcassada ,

What do you mean under "not posted to GL" ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

There are unposted and posted invoices and I only need posted. I only want paid vendor invoices.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Ok, and what are you calling "vendor transactions" what table are you referring to ?

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello avcassada,

It would be great if you could provide some additional information on your request probably by providing an example, as posted invoices might be paid and settled or unpaid.

Got a bit confused with the terms that you use and I believe a brief example might help.

Many thanks,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am looking for a listing of vendor invoices that have been paid and settled and posted to the general ledger. I am trying to pull purchase transactions to audit for proper controls and support the financial statements. I have VendTrans, VendTable, VendInvoiceInfoTable, as a few examples. I am an external auditor looking to pull data to support the audit.

An example would be something like VP000020 15.6200000000000000 2016-02-11 00:00:00.0 ADP INC

Does this help?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Any amounts paid to a vendor - I have VendTable, VendTrans, VendInvoiceInfoTable, and a few others.

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello avcassada,

Do you have a developer on hand that can help you with that or are you on your own?

Ludwig

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (1)

Report

Hi avcassada,

VendTrans(Table) has a field named as Closed.

This field defines if an invoice has been fully settled with payment.

So, to extract all fully settled transactions you can take where VendTrans.Closed = NoYes::Yes

and vice-versa

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

All by myself - I develop mostly in Peoplesoft/Oracle. This is my first experience with Dynamics.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

It appears mine has a date in this field so I would do any with a date during the year?

Was this reply helpful? Yes No