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

Microsoft Dynamics AX 2012 won’t support Oracle database

Current customers using Oracle database as their AX transactional database will be forced to switch to Microsoft SQL Server. It should be facilitated by Oracle to Microsoft SQL Server Data Migration Assistant for Microsoft Dynamics AX (PartnerSource link) – it allows to migrate AX4/AX2009 databases; conversion of Axapta 3 database is supported as a part of upgrade to AX2009

Overview of the tool from Partner source

The Oracle to Microsoft SQL Server Data Migration tool helps customers on Microsoft Dynamics AX with an Oracle database to migrate to a Microsoft SQL Server database. It supports Microsoft® Business Solutions-Axapta® 3.0, now part of Microsoft Dynamics®, Microsoft Dynamics AX 4.0 and Microsoft Dynamics AX 2009.

For Microsoft Dynamics AX 4.0 and Microsoft Dynamics AX 2009, it supports migration of database on same Microsoft Dynamics AX version i.e. Microsoft Dynamics AX 4.0 on Oracle to Microsoft Dynamics AX 4.0 on SQL Server. For Microsoft Business Solutions-Axapta, it supports upgrade from Microsoft Business Solutions-Axapta to Microsoft Dynamics AX 2009 as part of the upgrade. This framework is a replacement for the AXDBUpgrade step in the upgrade. Rest of the steps remain the same and need to be fully followed thru for complete upgrade as well as data migration.

The Oracle to Microsoft SQL Server Data Migration Tool provides the ability to multi thread the data transfer, while doing the necessary transformations. It is important to note that the toolset does not support not standard table migration (Tables that are not on the AOT). The toolset execution should be followed with rigorous testing for data validation as well as migration quality

Oracle to Microsoft SQL server Migration tool

Debug Enterprise Portal Code in Microsoft Dynamics AX

1. Log in to the server that is running the AOS.
2. Open the Microsoft Dynamics AX Server Configuration utility. To openStart > Control Panel > Administrative Tools > Microsoft Dynamics AX 2009 Server Configuration.
3. Create a new configuration that allows debugging.
3.a. Click Manage then Create configuration. Give a name to the new configuration in the Create Configuration window then click OK.
3.b. On the Application Object Server tab, select Enable breakpoints to debug code X++ code running on this server. Click on Apply button.
4. Click on OK button to close the configuration window. You will get a message that indicates AOS is going to be restarted.
5. Actually debugger works on the server where IIS is running and Enterprise Portal is hosted. I used a single system and all the things are on the same system. If you are accessing a system remotely using Terminal Services then from the Start menu click Run. Type mstsc /console in theOpen text box and click OK This opens a console session in Terminal Services. Console session is required to debug Ax EP
6. The World Wide Web Publish Service should be enabled.6.a. Open the Services window for the system. To open it Start > Control Panel > Administrative Tools > Services.
6.b. Right-click the World Wide Web Publishing Service and click Properties.
6.c. Click the Log On tab.6.d. Select Allow service to interact with desktop as shown below.
6.e. Click on OK button to close the properties window.
7. Open the web.config file located in:\Inetpub\wwwroot\wss\VirtualDirectories\\, here the port number of the Enterprise Portal site. If you forgot the port then there is a way to check it out. Open Ax client then Administration main menu > Setup > Internet > Enterprise Portal > Web sites, here you can see the port. 8. Now do the following:
8.a. Find out the compilation element then set the debug attribute to true.8.b. Save the changes.
9. Reset IIS by typing the iisreset command at the command-line window or type iisreset in Start > Run > Open text box then click on OK button.
10. Open the Microsoft Dynamics AX Configuration utility. To open it Start > Control Panel > Administrative Tools > Microsoft Dynamics AX 2009 Configuration.
11. Set the Application Object Server Instance drop-down menu to Business Connector (non-interactive use only).
12. Create a new configuration to allow debugging.12.a. Click on Manage button then click Create configuration. In the Create Configuration window, name the new configuration then click OK button.12.b. On the Developer tab, select Enable user breakpoints to debug code in the Business Connector and Enable global breakpoints to debug code running in the Business Connector or client then click on Apply button.
13. Click on OK button to close the configuration window.
14. Open the Microsoft Dynamics AX client.
15. On the Tools menu, click Options to display the Options window.
16. On the Development tab, select When Breakpoint from the Debug mode list box, and then click Apply. This enables debugging mode on the client.
17. Close the Options window.
18. Open the Microsoft Dynamics AX client.
19. Now decide the element you want to debug. Lets say you want to debug a class method. Open the method in code editor and write breakpointswhere you want to set a breakpoint.
20. Open the Debugger window manually. To open debugger manually click on Tools > Development Tools > Debugger.
21. Now execute the web form where the method has been invoked. It should work. On my system it is working perfectly.

You will find this information on other blogs to but i am placeing here so if you come to my blog and dont go information less. Following links are good in explaining this too.
http://daxdilip.blogspot.com/2009/03/how-to-debug-enterprise-portal-code-in.html

http://thieuquanghuy.wordpress.com/2011/06/10/configuration-to-debug-enterprise-portal-in-visual-studio-and-x-editor/

You would also needs a hotfix if you working on Windows 2008 with dyanamics ax 2009, for Ax 2012 you wont needs this.