Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report

(0) Share

Report

Posted on by D365FO DEV

406

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?

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Martin Dráb 237,805 Most Valuable Professional on at

Like (0)

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);

Was this reply helpful? Yes No
D365FO DEV 406 on at

Like (0)

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;

}

Was this reply helpful? Yes No

Join the conversation

Community site session details

Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates