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  

SharePoint 2013 Installation and Configuration Issues

During Installing SharePoint 2013 Prerequisites on Windows Server 2012 there was an error in installing Application Server Role , Web Server (IIS) Role : Configuration error

To resolve this error, go to windows\system32 directory.

Copy and rename servermanager.exe to servermanagercmd.exe

SQL query to extract primary address from CUSTTABLE

The following query can be used to extract the primary address from the CUSTTABLE table:

 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS  
 from CUSTTABLE  
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY  
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION  
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID  
 Order by CUSTTABLE.ACCOUNTNUM  

SQL query to extract financial dimension value from CUSTTABLE

The following SQL query can be used to extract a financial dimension value from the CUSTTABLE table:

 select CustTable.ACCOUNTNUM, SubscriberType.DISPLAYVALUE as SubscriberType  
 from CustTable  
 left outer join DIMENSIONATTRIBUTEVALUESETITEM AS SubscriberType ON SubscriberType.DIMENSIONATTRIBUTEVALUESET = CUSTTABLE.DEFAULTDIMENSION  
 left outer join DIMENSIONATTRIBUTEVALUE ON DIMENSIONATTRIBUTEVALUE.RECID = SubscriberType.DIMENSIONATTRIBUTEVALUE  
 left outer join DIMENSIONATTRIBUTE ON DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE and DIMENSIONATTRIBUTE.NAME = 'SubscriberType'  

Customer contact list for AX2012

Query to extract a quick customer contact list from Dynamcis AX 2012

 SELECT   CUSTTABLE.ACCOUNTNUM AS CUSTID, DIRPARTYTABLE.NAME AS CUSTNAME,   
            CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,   
            LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME, LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS  
 FROM     DIRPARTYTABLE AS DIRPARTYTABLE INNER JOIN  
            CUSTTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY INNER JOIN  
            DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY INNER JOIN  
            LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION  
 ORDER BY DIRPARTYTABLE.NAME