Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report

(0) ShareShare
ReportReport
Posted on by 404
Hi, im facing an issue with my ssrs report amount value. what is happening that if we have an invoice with 2 lines for example, with the SAME VOUCHER NUMBER, the amount value of the first line is coming correct, but for the second line its coming the same value as first line which is wrong.( check below screenshots)
 
as u can see in the second picturem the amount for the 2nd line is the same as the first line which is wrong. i believe the issue is with my rdp class with the relation. i also think a condition is missing since they have the same voucher number.
my relation is between two tables:taxtrans and ledgerjournaltrans.
 
 select firstonly TaxTrans
                where TaxTrans.JournalNum == LedgerJournalTrans.JournalNum &&
                    TaxTrans.Voucher == LedgerJournalTrans.Voucher;
any advise?
  • D365FO DEV Profile Picture
    D365FO DEV 404 on at
    Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report
    thank you martin, your answer helped, but for some reason, a new error is showing which is showing for excluding vat:
     
    as u can see in the picture below, the LCY Amount and amount in currency should be the same as the total amount.
    the field of lcy amount and amount in currency are:
     
    amount in currency:VendAmountLCY          
    LCY Amount:VendAmountCurr        
     
    here is my logic from the rdp class: 
     
     while select LedgerJournalTrans where  LedgerJournalTrans.JournalNum == ledgerJournalId && LedgerJournalTrans.Voucher == voucher
                {
                    if(ledgerjournaltrans.AccountType == LedgerJournalACType::Vend && LedgerJournalTrans.OffsetLedgerDimension == 0)
                    { 
    Ex_InvoiceJournalTmp.Ex_VATAmount       = LedgerJournalTrans.totalTaxAmountSingleLine(true,false,true);
    if(ledgerJournalTrans.AmountCurCredit == 0)
                        {
                            Ex_InvoiceJournalTmp.VendAmountLCY          = ((Ledgerjournaltrans.AmountCurDebit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)/100;
                            Ex_InvoiceJournalTmp.VendAmountCurr         = Ledgerjournaltrans.AmountCurDebit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount) ;
                   
                        }
                        else if(ledgerJournalTrans.AmountCurDebit == 0)
                        {
                            Ex_InvoiceJournalTmp.VendAmountLCY          = abs(((Ledgerjournaltrans.AmountCurCredit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)/100) *-1;
                            Ex_InvoiceJournalTmp.VendAmountCurr         = abs(Ledgerjournaltrans.AmountCurCredit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * -1;
                    
                        }
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report
    I looked at LedgerJournalTrans relation on TaxTrans table and according to it, the right relation is this:
    select firstonly taxTrans
        where taxTrans.SourceRecId == ledgerJournalTrans.RecId
           && taxTrans.SourceTableId == tableNum(LedgerJournalTrans);

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,969 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,842 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans