Deleting duplicate records via SQL

 WITH DUP_BARCODE (ITEMBARCODE, DUPCOUNT) AS  
 (SELECT ITEMBARCODE, COUNT(RECID) AS DUPCOUNT  
  FROM INVENTITEMBARCODE   
  GROUP BY ITEMBARCODE  
  HAVING COUNT(RECID) > 1)  
    
  DELETE FROM INVENTITEMBARCODE  
  FROM INVENTITEMBARCODE AS I1  
  JOIN DUP_BARCODE ON I1.ITEMBARCODE = DUP_BARCODE.ITEMBARCODE  
  WHERE I1.RECID != (SELECT TOP 1 RECID FROM INVENTITEMBARCODE AS I2 WHERE I2.ITEMBARCODE = I1.ITEMBARCODE ORDER BY I1.CREATEDDATETIME ASC)  

Get AOS server name for current session

The following code obtains the AOS server name for the current session:

 static void getServerNameForCurrentSession(Args _args)  
 {  
   sysClientSessions cliSessions;  
   sysServerSessions svrSessions;  
   ;  
   select svrSessions  
     exists join cliSessions  
       where cliSessions.SessionId == sessionID()  
         && cliSessions.ServerID == svrSessions.ServerId;  
   info(substr(svrSessions.AOSId, 1, strfind(svrSessions.AOSId, '@', 1, strlen(svrSessions.AOSId))-1));  
 }  

SELECTING latest INVENTRANS record by ItemId and InventDimId

On the project I'm currently working on we had a request to produce a list of the latest INVENTTRANS record per Item and InventDimId. After a little bit of work I came up with the following SQL query.

 SELECT DISTINCT INV1.ITEMID, INV1.INVENTDIMID, INV2.DATEFINANCIAL, INV2.QTY, INV2.COSTAMOUNTPOSTED FROM INVENTTRANS AS INV1  
 JOIN INVENTTRANS INV2 ON INV2.ITEMID = INV1.ITEMID and INV2.INVENTDIMID = INV1.INVENTDIMID  
 WHERE INV2.RECID =  
 (SELECT TOP 1 RECID FROM INVENTTRANS WHERE ITEMID = INV2.ITEMID and INVENTDIMID = INV2.INVENTDIMID ORDER BY DATEFINANCIAL DESC, CREATEDDATETIME DESC)  

Master scheduling run for All Items not picking up level 0 items

Master scheduling had been working fine for a number of days following Go-live, but then something happening and alot of items were not being picked up in the master scheduling run. The symptoms reported by the user was that the process was only running for 10 minutes, where initially it was taking 2 hours to run. Also, no errors were being reported. I suspected a data issue and after a several hours of investigation I finally found the issue. Somehow an InventTable record with a blank ItemId had been created in the database. In the Master Scheduling code that builds the item list for each level there's an If statement that checks the ItemId and if it's blank exits the loop and because this blank item was the first item processed for the level, no items were being selected for this level for the master scheduling run.

Following on from the discovery of this issue and the workaround of deleting the blank item from InventTable, the blank item kept reappearing in the database and it was decided to modify the Master Planning code to discard items with a blank ItemId.

This was done by making a simple change to the createListsFromQueryRun method of the ReqTransCache_Periodic class which builds the item lists used by master planning.

   while (runQuery.next())  
   {  
     inventTable = runQuery.get(tablenum(InventTable));  
    
     // Discard any items with a blank ItemId  
     if (inventTable.ItemId == "")  
     {  
       continue;  
     }  
    
     if (!levelItemMap.exists(inventTable.bomLevel))  
     {  
       itemSet = new Set(Types::String);  
       levelItemMap.insert(inventTable.bomLevel, itemSet);  
     }  
     else  
       itemSet = levelItemMap.lookup(inventTable.bomLevel);  
    
     itemSet.add(inventTable.ItemId);  
    
   }  

Locking records in SQL Server 2008

 USE DynamicsAx3_DEV  
 GO  
    
 SET TRANSACTION ISOLATION LEVEL  
 REPEATABLE READ  
 GO  
    
 BEGIN TRANSACTION  
 select * from RCMCHANGES (UPDLOCK) where STOREID = '0969' or STOREID = '0976'  
    
 ROLLBACK TRANSACTION  
 GO  

Custom FormDatasource lookup

The following sample demonstrates how to add custom lookup to field on an AX form

 public void lookup(FormControl _formControl, str _filterStr)  
 {  
   SysTableLookup     sysTableLookup = SysTableLookup::newParameters(tablenum(InventItemBarcode), _formControl);  
   Query              query = new Query();  
   QueryBuildDataSource  qbds;  
   ;  
    
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, ItemBarcode), true);  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, Description));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_Sell_ItemId));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_UnitId));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_Primary));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_Active));  
    
   qbds = query.addDataSource(tablenum(InventItemBarcode));  
   qbds.addRange(fieldnum(InventItemBarcode, ItemId)).value(queryValue(RCMJournalItemTrans.ItemId));  
    
   sysTableLookup.parmQuery(query);  
   sysTableLookup.performFormLookup();  
 }  

SQL script for updating AX tables

The following SQL code is an example how to insert records into an AX table including generation of RecId's. One point to note is the SYSTEMSEQUENCES record for a table is only created when the first record is added to an AX table through the AX client.

 ALTER PROCEDURE loadLIQ_StoreItemVendorTable AS  
 DECLARE @NEXTVAL BIGINT,  
       @ROWCOUNT  BIGINT  
         
 SELECT DISTINCT 'sto' AS DATAAREAID, 0 AS RECVERSION, IDENTITY(BIGINT,0,1) AS RECID, RPT_T_VendorStoreItem.StoreID AS RCMSTOREID,  
 RPT_T_VendorStoreItem.ItemID AS ITEMID, EndVendorAccountNumber AS VENDACCOUNT, CHAIN.GROUPINGID AS CHAIN, INVENTITEMBARCODE.LIQ_Sell_ItemID, INVENTTABLE.ItemGroupId AS ITEMGROUPID  
 INTO #TEMP  
 FROM RPT_T_VendorStoreItem, INVENTTABLE, INVENTITEMBARCODE, RCMGROUPING AS STORE  
 INNER JOIN RCMSTORETABLE ON STORE.REFRECID = RCMSTORETABLE.RECID  
 INNER JOIN RCMGROUPING AS CHAIN ON CHAIN.GROUPINGID = STORE.GROUPINGARRAY2_  
 WHERE STORE.ROOTPARENT = (SELECT LIQ_GROUPINGIDCORELOCATIO30002 FROM RCMPARAMETERSVIRTUAL WHERE DATAAREAID ='CLV') and  
      RCMSTORETABLE.STOREID = RPT_T_VendorStoreItem.StoreID and   
      INVENTTABLE.ITEMID = RPT_T_VendorStoreItem.ItemID and  
      INVENTITEMBARCODE.ITEMID = RPT_T_VendorStoreItem.ItemID and  
      INVENTITEMBARCODE.LIQ_UNITID = RPT_T_VendorStoreItem.UnitOfMeasure and  
      INVENTITEMBARCODE.LIQ_ACTIVE = 1 and  
      INVENTITEMBARCODE.LIQ_SELL_ITEMID <> ''  
 ORDER BY RPT_T_VendorStoreItem.StoreID, RPT_T_VendorStoreItem.ItemID  
    
 SELECT @NEXTVAL = NEXTVAL FROM SYSTEMSEQUENCES WITH(UPDLOCK, HOLDLOCK)  
 WHERE ID = -1 and TABID = 30345  
    
 INSERT INTO LIQ_STOREITEMVENDOR  
 (DATAAREAID, RECVERSION, RECID, RCMSTOREID, ITEMID, VENDACCOUNT, CHAIN, LIQ_SELL_ITEMID, ITEMGROUPID)  
 SELECT DATAAREAID, RECVERSION, RECID = RECID + @NEXTVAL, RCMSTOREID, ITEMID, VENDACCOUNT, CHAIN, LIQ_SELL_ITEMID, ITEMGROUPID  
 FROM #TEMP  
    
 SELECT @ROWCOUNT = COUNT(*) FROM #TEMP  
    
 UPDATE SYSTEMSEQUENCES  
 SET NEXTVAL = @NEXTVAL + @ROWCOUNT  
 WHERE ID = -1 and TABID = 30345  
 GO  

X++ code for adjusting tax on journals and free text invoices

 static void ExampleLedgerJournalTaxAdjust(Args _args)  
 {  
   LedgerJOurnalTrans         ledgerJournalTrans;  
   TaxLedgerJournalCalculate  taxLedgerJournalCalculate;  
   TaxLedgerJournal           taxLedgerJournal;  
   TaxRegulation              taxRegulation;  
   ;  
   
   ttsbegin;  
   
   // Find a ledgerJournalTrans record  
   select firstonly LedgerJournalTrans  
   where LedgerJournalTrans.JournalNum == "000185_010";  
   
   
   taxLedgerJournal = TaxLedgerJournal::construct(TaxJournalCall::Journal, ledgerJournalTrans, null);  
   taxRegulation    = new TaxRegulation();  
   taxLedgerJournal.calcAndPost();  
   taxRegulation.setTax(taxLedgerJournal);  
   taxRegulation.createSumsFromTmp();  
   taxRegulation.allocateAmount(6.99);  
   taxRegulation.saveTaxRegulation();  
   
   ttscommit;  
 }  
   
 static void FreeTextTaxAdjust(Args _args)  
 {  
   CustInvoiceTable    custInvoiceTable;  
   CustInvoiceCalcTax  custInvoiceCalcTax;  
   TaxFreeInvoice      taxFreeInvoice;  
   TaxRegulation       taxRegulation;  
   ;  
   
   ttsbegin;  
   
   select firstonly custInvoiceTable  
   where custInvoiceTable.InvoiceId == "Inv135";  
   
   custInvoiceCalcTax = new CustInvoiceCalcTax_Table(custInvoiceTable);  
   taxFreeInvoice     = new TaxFreeInvoice(custInvoiceCalcTax);  
   taxFreeInvoice.calc();  
   taxRegulation = TaxRegulation::newTaxRegulation(taxFreeInvoice);  
   taxRegulation.allocateAmount(35.77);  
   taxRegulation.saveTaxRegulation();  
   
   ttscommit;  
 }  

Checking to see if caller is a form

I had this requirement the other day to set the filter value on the LedgerJournalTable form when it was called from a new form that had been created.

This new form contained a JournalId field and I wanted the users to be able to right-click goto main table to open the LedgerJournalTable showing the journal record.

Because the journal may or may not be posted I needed to set the standard filter on the LedgerJournalTable form to show all journals (by default when the form is opened it shows 'Open' Journals only) So, I added some code to the init method of the LedgerJournalTable form to check the element.args().caller().name for my new form and then set the filter.

This worked a treat, except I managed to break the journal lookups in other areas of the system e.g. Vendor transactions -> Original document.

To cut a long story short the Original Documents function is a class (which does not have a name() method that also calls the LedgerJournalTable form.

To get around this issue I used the code below which checks to see if the caller is a form before calling the name() method.

 if (element.args() && element.args().caller())  
 {  
     // Make sure this was via a form.  
     if(SysDictClass::is(element.args().caller(), classnum(FormRun)))  
     {  
         // Cast the caller and make sure it is the right form.  
         callerForm = element.args().caller();  
   
         if (callerForm.name() == formstr(LIQ_RegisterCashControlDetails))  
         {  
             allOpenPostedField.selection(AllOpenPosted::All);  
         }  
     }  
 }  

Handling AX UTC dates in SQL Server

SELECT *, DATEADD(hour,DATEDIFF(hour,GETUTCDATE(),GETDATE()),MODIFIEDDATETIME) As ModifiedDateTime from SYSRECORDLEVELSECURITY

Dynamics AX 4.0 SQL 2005 OLAP issue

The client reported an issue with not being able to process OLAP cubes after returning to work after being away on holiday.

It was pretty clear from the error, what the issue was - missing OLAP client components and after a quick investigation it was discovered that the client's PC had been rebuilt with Windows 7.

But after installing the required OLAP client components we still couldn't update OLAP cubes.

The error we were now getting was:

Method 'connect' in COM object of class '{B492C386-0195-11D2-89BA-00C04FB9898D}' returned error code 0x800A0005 () which means: The connection string to repository needs to be specified in the 9.0 server properties (see ... section in msmdsrv.ini file from Analysis Services 9.0).

Upon further investigation it was discovered that the OLAP version which is stored in the AX database was reset to SQL 2000. This is apparently a known issue that sometimes happens when you try to connect to OLAP from a PC that doesn't have the client tools installed. Even if you install them later on this doesn't fix the issue. You need to go into the OLAPServer table in AX and manually set the OLAP version back to SQL 2005.