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