Cannot Create a Record in Balance (CustVendTmpOpenTransBalances). Amount: 0,00. The Record Already Rxists. – SpecTrans Issue

In my previous post I wrote about vendor settlement issue related to inconsistent data. In this post the issue is more or less similar but in this case it is impossible to open the Vendor settlement form (Accounts payable > Common > All vendors > Tab: Invoice > Settle open transactions). When user try to open the form it ends up with error message:

 

“Cannot create a record in Balance (CustVendTmpOpenTransBalances). Amount: 0,00. The record already exists.”

 

Again the problem is the inconsistent data in the SpecTrans table. This table contains records that connect vendor records (VendTable) with vendor open transactions (VendTransOpen). These records are used to fill a temporary table called(CustVendTmpOpenTransBalances) which has unique index on TransOpenRecId field.

 

This filed is filled with VendTransOpen RecId value. SpecTrans table contains reference to non-existing VendTransOpen records so consequently TransOpenRecId field in CustVendTmpOpenTransBalances table is populated 0. The unique index restriction thrown an error when AX tries to insert the second record with the same 0 value.

 

The solution again is to cleanup inconsistent SpecTrans data. I created a job to cleanup the inconsistent SpecTrans. In my job the records are filtered by vendor but you can remove the range in order to execute for all vendors.

 

static void VendTransOpenSpecTransCleanup(Args _args)
{
VendTransOpen vendTransOpen; // tableId: 866
SpecTrans specTrans;
ttsBegin;
while select forUpdate SpecTrans
where  specTrans.SpecCompany == ‘DataAreaId’
&& specTrans.SpecTableId == 505         // VendTable ID
&& specTrans.SpecRecId   == 11111111 // Vendor RecId
&& SpecTrans.RefTableId  == 866
notExists join vendTransOpen
where  vendTransOpen.TableId    == specTrans.RefRecId
&& vendTransOpen.RecId      == specTrans.RefRecId
&& VendTransOpen.dataAreaId == SpecTrans.RefCompany
{
info(strFmt(‘%1’,specTrans.RecId));
SpecTrans.delete();
}
ttsCommit;
}

Problem with Vendor Settlement – SpecTrans Issue

One of my clients reported an issue that the settlement is not possible for vendor transactions (Accounts payable > Common > All vendors > Tab: Invoice > Settle open transactions). The records were marked with red exclamation and when the user try to mark the record gets the an error message:

 

“This transaction has been marked for settlement by another user.”

 

This error message is very clear and when I saw it I said that someone else already marked the transaction for settlement. The interesting part came when they said that they are sure that this is not correct.

 

Using the Specifications form (Inquires > Specifications) I tried to find the transaction that was marked against. The result was no transaction in the specification form.

 

On the other side the “Is marked” column shows that there is a related transaction.

 

This column is actually a display method that use SpecTrans table to decide if the transaction is already marked.

 

Select firstonly crossCompany RecId from specTrans where

(specTrans.SpecCompany != specCompany ||
specTrans.SpecTableId != specTableId ||
specTrans.SpecRecId != specRecId) &&
specTrans.RefCompany == _refCompany &&
specTrans.RefTableId == _refTableId &&
specTrans.RefRecId == _refRecId;

 

(Ref fields consist the relationship with Vendor open transaction in our case – VendOpenTrans)

 

This findings suggested that there is data inconsistency.

 

I created a simple job to find the marked lines that are inconsistent:

 

static void findMarkedTrans(Args _args)
{
VendTransOpen VendTransOpen;
SpecTrans     specTrans;
while select VendTransOpen
where VendTransOpen.AccountNum == ‘VendAccount’
join specTrans
where specTrans.RefRecId == VendTransOpen.RecId
&&    specTrans.RefTableId == tableNum(VendTransOpen)
&&    specTrans.RefCompany == ‘DataAreaId’
{
info(strFmt(‘%1 %2 %3’, specTrans.SpecCompany, tableId2name(specTrans.SpecTableId), specTrans.SpecRecId));
}
}

 

Using the Spec field I found out the records that marked those transactions. Then I just open the table I got from the job (in my case LedgerJournalTrans) and filtered by Record ID. As you assume, there we no data!

 

If you check the documentation for the SpecTrans table you can see that the description is “The SpecTrans table contains records that relate to transactions for the settlement and settlement reversal processes. Records are deleted after they are processed.” So, in this case SpecTrans table was not “cleaned up” properly.

 

Cleaning up the SpecTrans table from inconsistent records resolved the issue.

 

It is hard to say what is the reason for this issue. Probably it was an AOS crush during the time of posting. Unfortunately this happens sometimes and the consequences can be much bigger.

 

I experienced a case where the AOS crush caused to be impossible to post vendor invoices. The solution in this case was pretty similar, clean up a “temporary table”.

 

Another one resulted in even worse situation. The inventory transactions (Invoiced lines) and the postings in the ledger were not inconsistent.