Jim, If VendorTable is not shared between virtual companies then something like:
SELECT vtable.Name, vt.AccountNum, vt.TransDate, vt.Voucher, vt.invoice, vt.txt, vt.AmountCur, vt.SettleAmountCur, vt.AmountMst, vt.SettleAmountMst, vt.CurrencyCode, vt.dueDate, vt.ExchAdjustment, vt.dataareaid FROM VendTrans vt INNER JOIN VendTransOpen vto ON vto.refrecid = vt.recid INNER JOIN VendTable vtable ON vt.dataareaid = vtable.dataareaid AND vt.accountnum = vtable.accountnum will give you all *open* vendor transactions and if you want open and closed transactions then change the join on VendTransOpen from INNER JOIN to LEFT OUTER JOIN. --Adam On Sat, Jun 5, 2010 at 6:30 PM, Jim O'Brien <jim.obr...@sas.com> wrote: > > > Our auditors are looking for a report of payments and invoices by > vendors. Is there a way to right a sql against the vendtrans and possibly > the vend settlement tables to provide a list of payments and also invoices, > not paid, per vendor? > > > > > > Is there a better document or book available than, the following. This > document does not provide enough detail. Thanks > > > > Dynamics AX 4.0 data model overview > > Prepared for > > Microsoft Corporation > > Tuesday, 18 November 2008 > > Version .1 Draft > > Prepared by > > Fred Viidul > > MBS Consultant > > >
