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