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)
while select VendTransOpen
where VendTransOpen.AccountNum == ‘VendAccount’
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…