不能在“源文件行”上创建数据定义语言命令：会计分配 / cannot create a data definition language command on “source document line” : Accounting distribution

error “cannot create a data definition language command on “source document line””

In one of the client we had the requirement to always post the Pending vendor invoice on today’s date, regardless of when it was created. so we did the simple customization to update the TransDate before code initiate the posting process.

vendInvoiceInfoTable.selectForUpdate(true);
vendInvoiceInfoTable.TransDate = today();
vendInvoiceInfoTable.update();

But for this requirement is not that simple, somehow it is not updating the Accounting distribution and keeping the entries on older date in AccountingDistribution table, hence causing the error which is title of this blog.

After lots of investigation i reached to the conclusion, and this small job help me finding the cause and fixing it by creating the new Accounting Distribution record in today’s date and deleting the older one.

 static void VendInvAccDistributionCorrection(Args _args)  
 {  
   AccountingDistribution AccountingDistribution, AccountingDistributionCopy;  
   vendInvoiceInfoLine vendInvoiceInfoLine;  
   VendInvoiceInfoTable vendinvoiceInfoTable;  
   select firstonly vendInvoiceInfoTable where vendInvoiceInfoTable.Num == '1234';// todo invoice number to remove the matching  
   if (vendInvoiceInfoTable.RecId)  
   {  
     ttsBegin;  
     vendInvoiceInfoTable.selectForUpdate(true);  
     vendInvoiceInfoTable.TransDate = today();  
     vendInvoiceInfoTable.update();  
     ttsCommit;  
     while select vendInvoiceInfoLine  
       where vendInvoiceInfoLine.TableRefId == vendinvoiceInfoTable.TableRefId  
         && vendInvoiceInfoLine.ParmId == vendinvoiceInfoTable.ParmId  
     {  
       select firstOnly * from AccountingDistributionCopy order by number desc  
         where AccountingDistributionCopy.SourceDocumentHeader == vendinvoiceInfoTable.SourceDocumentHeader  
           && AccountingDistributionCopy.SourceDocumentLine == VendInvoiceInfoLine.SourceDocumentLine;  
       AccountingDistribution.SourceDocumentLine = VendInvoiceInfoLine.SourceDocumentLine;  
       AccountingDistribution.SourceDocumentHeader = vendinvoiceInfoTable.SourceDocumentHeader;  
       AccountingDistribution.TransactionCurrency = VendInvoiceInfoTable.CurrencyCode;  
       AccountingDistribution.LedgerDimension = AccountingDistributionCopy.LedgerDimension;  
       AccountingDistribution.AccountingLegalEntity = AccountingDistributionCopy.AccountingLegalEntity;  
       AccountingDistribution.MonetaryAmount = AccountingDistributionCopy.MonetaryAmount;  
       AccountingDistribution.AccountingDate = today();  
       if (vendInvoiceInfoLine.LineAmount && AccountingDistribution.MonetaryAmount)  
       {  
         AccountingDistribution.AllocationFactor = AccountingDistribution.TransactionCurrencyAmount/VendInvoiceInfoLine.LineAmount;  
       }  
       else  
       {  
         AccountingDistribution.AllocationFactor = 1;  
       }  
       AccountingDistribution.AmountSource = DistributionAmountSource::PrimaryAmount;  
       //axAccountingDistribution.parmMonetaryAmountType(MonetaryAmount::ExtendedPrice);  
       AccountingDistribution.insertAccountingDistribution(true);  
     }  
       delete_from accountingDistribution  
       where accountingDistribution.SourceDocumentHeader == vendinvoiceInfoTable.SourceDocumentHeader  
       && accountingDistribution.AccountingDate != today();  
   }  
   else{  
     info(strFmt('invoice not found in %1 company', curext()));  
   }  
 }