Vendor email address list from AX 2012 R3

SELECT   VENDTABLE.ACCOUNTNUM AS VENDORID, DIRPARTYTABLE.NAME AS NAME,  
         LOGISTICSELECTRONICADDRESS.LOCATOR AS EMAILADDRESS
 FROM     DIRPARTYTABLE AS DIRPARTYTABLE INNER JOIN
            VENDTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY INNER JOIN
            DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY INNER JOIN
            LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION
 where LOGISTICSELECTRONICADDRESS.TYPE = 2  
 ORDER BY DIRPARTYTABLE.NAME

Another instance of CIL generation is already in progress. Please wait for the operation to complete before retrying.

When a CIL compile doesn't complete properly subsequent CIL's may fail with the following error: "Another instance of CIL generation is already in progress. Please wait for the operation to complete before retrying.".

To rectify this issue by delete the following record from the SYSLASTVALUE table

select * from SYSLASTVALUE where ELEMENTNAME =  'CIL Generation'

delete from SYSLASTVALUE where ELEMENTNAME = 'CIL Generation'

Cannot resolve the collation conflict between...

I was trying to run a query between 2 tables from different databases and got the following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

To get around this issue you can still join between them by using the COLLATE command to choose the collation you want

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE Latin1_General_CI_AS 

or using the default database collation

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE DATABASE_DEFAULT

AX2012 R3 ASync Client not pulling data back from the store

Got the following error this week at one of our retail stores:

Microsoft Dynamics AX Retail : Async Client CommerceDataExchangeAsyncClientServiceException when converting job defination to request header. Error Details: System.NullReferenceException: Object reference not set to an instance of an object.

This issue was resolved by changing the RERUN status on the UPLOADSESSIONS table back to None.

Importing Purchase Orders in Dynamics AX 2012 is extremely slow

On a project that I was working on, I was required to import a large number of purchase orders. Some of these orders contained several hundred lines and in some cases over 1,000 lines. These orders were taking an extremely long time to import and it got to a stage where each line was taking over 30-40 seconds to process. After performing an analysis of the code, I tracked the issue to the insert method on the PurchLine table. Dynamics AX 2012 runs a recalculation of line distributions for the entire order every time a new line is inserted. The solution was to modify the code to prevent this full recalculation from running during the import and use a batch job at the end to run the recalculation after all the lines had been imported.

It's actually a very small change to make - just need to update the default value of one of the parameters on PurchLine.Insert method "_skipPurchTableUpdate" from False to True.



After importing the PO's, you can use a job to update the distributions by calling:

purchTable.updateFromPurchLines(true);

Query for listing duplicate Sales invoice numbers

 with InvDups as (select INVOICEID from CustInvoiceJour  
 where CustInvoiceJour.DATAAREAID = 'JWC'  
 group by INVOICEID having (count(INVOICEID) > 1))  
 select InvDups.INVOICEID, SALESID, CREATEDDATETIME, CREATEDBY from InvDups  
 join CustInvoiceJour on CustInvoiceJour.INVOICEID = InvDups.INVOICEID  
 Order by InvDups.INVOICEID, CREATEDDATETIME  

SQL Query for obtaining min and max sizes from product variants

 With Sizes_CTE (Product, DisplayOrder, Size)  
 AS (  
 select DISPLAYPRODUCTNUMBER Product, RETAILDISPLAYORDER DisplayOrder , NAME Size from ECORESPRODUCTMASTERDIMENSIONVALUE   
 inner join ECORESSIZE on ECORESSIZE.RecId = SIZE_  
 inner join ECOResProduct on EcoresProduct.RECID = SIZEPRODUCTMASTER)  
 select Z.Product, Concat(max(Z.MinSize), ' - ', max(Z.MaxSize)) SizeGroup from  
 (Select Product, Size MinSize, '' MaxSize from Sizes_CTE  
 where Sizes_CTE.DisplayOrder = (select min(DisplayOrder) from Sizes_CTE b where b.Product = Sizes_CTE.product )  
 union   
 Select Product, '' MinSize, Size MaxSize from Sizes_CTE  
 where Sizes_CTE.DisplayOrder = (select max(DisplayOrder) from Sizes_CTE b where b.Product = Sizes_CTE.product )) AS Z  
 group by Z.Product  
 order by Z.Product