Microsoft Dynamics AX (Archived)

How to get settled/closed invoices against a voucher on vendor payment journal posted transaction?

(0) Share

Report

Posted on by Rana Anees

1,813

Hii,

I am developing Payment Voucher Report. RDP and SSRS.

Navigation (AP->Payments->Payment journal -> a posted journal -> Lines -> Print -> Payment Voucher Report).

The report has header and lines temp tables.

Header temp table will have Vendor id, name, bank, iban, total payment amount, amount in words, payee bank etc and I got all this data.

Line temp table will have all invoices settled in this voucher.

Line temp table fields: InternalInvoiceId, Invoice, CurrencyCode, PurchID, Full Invoice Amount, Remaining Amount, Amount to Apply etc.

I found the relation b/w LedgerJournalTrans and VendTrans. But this is returning LedgerJournalTrans transactions not invoices.
select vendTrans where vendTrans.LastSettleVoucher == ledgerJournalTrans.Voucher && vendTrans.RecId == LedgerJournalTrans.VendTransId;

Question: But I need Invoices transactions in VendTrans table for the posted voucher.

Please help to achieve it in x++ code.

Thanks,

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!

Hi,

VendInvoiceJour and VendInvoiceTrans are the tables that hold the vendor invoice data and you can get data through the following relations

VendTrans --> vendInvoiceJour

VendTrans.Voucher == VendInvoiceJour.LedgerVoucher // Voucher relationship

VendTrans.AccountNum == VendInvoiceJour.InvoiceAccount

VendTrans.TransDate == VendInvoiceJour.InvoiceDate

VendinvoiceJour --> vendinvoiceTrans

VendInvoiceTrans.PurchID == VendInvoiceJour.PurchId

VendInvoiceTrans.InvoiceId == VendInvoiceJour.InvoiceId

VendInvoiceTrans.InvoiceDate == VendInvoiceJour.InvoiceDate

VendInvoiceTrans.numberSequenceGroup == VendInvoiceJour.numberSequenceGroup

VendInvoiceTrans.InternalInvoiceId == VendInvoiceJour.InternalInvoiceId

Thanks,

Chaitanya Golla

Was this reply helpful? Yes No
Brandon Wiese 17,790 on at

Like (0)

Report
Copy link

Link copied!

I don't see the point in fetching the LedgerJournalTrans record at all.

What you need to do is start from a payment VendTrans record, join to all VendSettlement records (linking payments to invoices), and to their VendTrans records (which will then be invoices).

The problem with LastSettleVoucher is that there can only be one "last" settlement, where there can be many settlements (an invoice can be paid over multiple checks).

Was this reply helpful? Yes No
Rana Anees 1,813 on at

Like (0)

Report
Copy link

Link copied!
Hii,

The total vendor invoice amount is 10,000 and we partially paid 6,000 from AP->Payment Journal.

How I can get in x++ Invoice Total amount, Remaining amount and Paid Amount.

I am trying as following but not getting...

if (ledgerJournalTrans.AccountType == LedgerJournalACType::Vend && ledgerJournalTrans.AmountCurDebit!=0 && ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Bank && ledgerJournalTrans.offsetAccountName()!="") { select vendTrans where vendTrans.Voucher == ledgerJournalTrans.Voucher; vendTransDetails = new CustVendTransDetails(vendTrans); vendTransDetails.setCustVendTrans(vendTrans); vendTransDetails.setTransDate(ledgerJournalTrans.TransDate == DateNull() ? systemDateGet() : ledgerJournalTrans.TransDate); [amountCurOpen, amountMSTOpen] = vendTransDetails.amountCurMSTSettled(); if(vendTrans.AmountCur == vendTrans.SettleAmountCur) { info("Amount Settled"); info(strFmt("Invoice Total Amount: %1 - Not Paid Yet: %2 - To be Paid: %3", vendTrans.AmountCur, vendTrans.AmountCur - amountCurOpen, ledgerJournalTrans.AmountCurDebit)); } else { info("Amount Not Settled"); } }

Result: Invoice Total Amount: 6,000.00 - Not Paid Yet: 0.00 - To be Paid: 6,000.00

But Invoice total amount is 10,000.

Where I am making mistake.

Please guide.

Thanks

Was this reply helpful? Yes No
Rana Anees 1,813 on at

Like (0)

Report
Copy link

Link copied!

Help please...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

There is no method remainAmountCur under VendorTrans, in other table?

Was this reply helpful? Yes No