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()));  
   }  
 }  
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: