I have been asked to generate a file with the following information from AX 2009:
- Vendor Number
- Vendor Invoice Number
- Vendor Invoice Date
- Purchase Order ID
- Purchase Order Line
- AP Document Number (ie. Check number...)
- AP Document Date (ie. Date check was issued)
- Payment Amount
Has anyone else done something like this or come across a report that gives this information?
is It best to do something like this?
LedgerJournalTrans
inner join
VendTrans
on LedgerJournalTrans.dataAreaId = VendTrans.dataAreaId and
LedgerJournalTrans.VendTransId = VendTrans.RecId
inner join
VendTrans as VendTransOffset
on
VendTransOffset.OffsetRecId = VendTrans.RecId
inner join
VendInvoiceJour
on VendTransOffset.DATAAREAID = VendInvoiceJour.dataAreaId and
VendTransOffset.Voucher = VendInvoiceJour.LedgerVoucher
inner join
VendInvoiceTrans
on VendInvoiceJour.dataAreaId = VendInvoiceTrans.dataAreaId and
VendInvoiceJour.InvoiceId = VendInvoiceTrans.InvoiceId and
VendInvoiceJour.PurchId = VendInvoiceTrans.PurchId and
VendInvoiceJour.InvoiceDate = VendInvoiceTrans.InvoiceDate and
VendInvoiceJour.numberSequenceGroup = VendInvoiceTrans.numberSequenceGroup and
VendInvoiceJour.internalInvoiceId = VendInvoiceTrans.internalInvoiceId
where LedgerJournalTrans.TransactionType = 15 and
LedgerJournalTrans.AccountType = 2 and
VendInvoiceJour.PurchId <> ''
