Cannot create a record in Balance (CustVendTmpOpenTransBalances). Amount: 0,00. The record already exists. – SpecTrans issue
Published On: October 20, 2016Categories: Technical
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
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
Allow All Cookies
Third Party Embedes
Third-party cookies are cookies that are set by other websites, not this one. This website may use embedded content from other websites, such as YouTube or Google maps. Your consent is required to view content from these websites embedded on this site.