Thursday, October 17, 2013

Summary update "Invoice account" doesn't work for intercompany sales orders


Hello altogether

I have discovered a strange behavior of AX (AX2012) with regards to intercompany orders and summary update.

Our customer has two subsidiaries and ships packages to the end customers (persons, further customers) in name of its clients (companies, further accounts). Sometimes one subsidiary (I will mention the company name – 101) ships packages to the customers of second subsidiary’s (102) accounts. We create an intercompany direct delivery order for that. Therefore, the whole supply chain looks like this:

SO 101 - PO 102 - SO 102

Intercompany is configured to post packing slip (product receipt) through the whole chain.
Picking is performed once a day and sometimes it comes out, that there are several orders for one customer, which are shipped together in one package. We summarize these orders into one packing slip and consider, that both PO 102 and SO 102 will also have a single delivery document posted (product receipt and packing slip).

Issue 1:

At first we used automatic summary update in both modules (Sales and Purchase):
Doing that we faced some severe performance problems, related to the query, choosing lines for update. There are more than a million customers in each company. Automatic summary update uses a sub table SalesJournalAutoSummary, containing one row per document type, storing update parameters in the customer master and on the sales order, to enable flexible summary of different document types:


The query, created inside the class SalesFormLetterParmData then looks like this:

SELECT FORUPDATE * 
 FROM SalesParmSubTable(SalesParmSubTable) 
 ORDER BY SalesTable.InvoiceAccount ASC, 
                 SalesTable.CurrencyCode ASC, 
                 SalesParmSubTable.TableRefId ASC 
 WHERE ((ParmId = N'101-005992')) 
EXISTS JOIN * 
 FROM SalesParmLine(SalesParmLine) 
 WHERE SalesParmSubTable.ParmId = SalesParmLine.ParmId AND 
              SalesParmSubTable.TableRefId = SalesParmLine.TableRefId AND 
              SalesParmSubTable.OrigSalesId = SalesParmLine.OrigSalesId 
JOIN * 
 FROM SalesTable(SalesTable) ON SalesParmSubTable.OrigSalesId = SalesTable.SalesId 
JOIN * 
 FROM SalesJournalAutoSummary(SalesJournalAutoSummary) 
 ON SalesTable.AutoSummaryModuleType = SalesJournalAutoSummary.ModuleType AND 
 (((
    (
     (SalesTable.AutoSummaryModuleType == 0) && 
     (SalesJournalAutoSummary.CustAccount == SalesTable.CustAccount)
            ) || 
           (
            (SalesTable.AutoSummaryModuleType == 1) && 
            (SalesJournalAutoSummary.SalesId == SalesTable.SalesId)
         )))
        ) AND ((DocumentStatus = 5))

If you select summary update = “Invoice account”, then this query will look like this:

SELECT FORUPDATE * 
 FROM SalesParmSubTable(SalesParmSubTable) 
 ORDER BY SalesTable.InvoiceAccount ASC, 
                 SalesTable.CurrencyCode ASC, 
                 SalesParmSubTable.TableRefId ASC 
 WHERE ((ParmId = N'101-005992')) 
EXISTS JOIN * 
 FROM SalesParmLine(SalesParmLine) 
 WHERE SalesParmSubTable.ParmId = SalesParmLine.ParmId AND 
              SalesParmSubTable.TableRefId = SalesParmLine.TableRefId AND 
              SalesParmSubTable.OrigSalesId = SalesParmLine.OrigSalesId 
JOIN * 
 FROM SalesTable(SalesTable) ON SalesParmSubTable.OrigSalesId = SalesTable.SalesId 


So it’s the same, but without SalesJournalAutoSummary and its complex range.
Due to the fact, that it is an intercompany direct delivery, we have a double slow down: the query is executed for both companies.

So, if you have a really huge customer master (usual for retail companies), please rethink using automatic summary update


So we gave up an idea to use the automatic summary and switched to a summary by invoice account. In fact, invoice account and currency were the group fields for summary.

Issue 2:

It comes out, that the summary does not work for intercompany orders in case of summary update option “Invoice account”. Funny, but if you choose the option “Automatic” and select only two fields “Invoice account” and “Currency” to summarize by, it works fine! The error is in this code (\Classes\SalesFormletterParmData\reArrangeLines, line 5, follow my comments):
    
    //-------------------------------------------------
    // it’s executed only for non-intercompany orders or
    // if summary update is “Automatic” or “Order”. 
    //---------------------------------------------------
    if (!salesTable.isInterCompanyOrder()
    ||   salesParmUpdate.SumBy == AccountOrder::Auto
    ||   salesParmUpdate.SumBy == AccountOrder::Journal)
    {
        if (this.createNewJournal(localSalesParmSubTable))
        {
            //-------------------------------------------------
            // responsible for storing summary information (is done only once for the first order)
            //---------------------------------------------------
            if (!salesParmUpdate.SumSalesId)
            {
                salesSummary.summarySalesId (salesTable.SalesId);
                salesSummary.summaryTableRef(formletterParmData::getNewTableRefId());
            }
            oldSalesParmTable = localSalesParmSubTable.salesParmTable();
            if (oldSalesParmTable.RecId && _queryRun.changed(tableNum(SalesParmSubTable)))
            {
                salesParmTableReArrange.data(oldSalesParmTable);
                if (salesParmTableReArrange.SalesId             != salesTable.SalesId)
                {
                    this.updateParmTable(salesParmTableReArrange, salesTable);
                }
                salesParmTableReArrange.TableRefId = salesSummary.summaryTableRef();
                this.insertParmTable(salesParmTableReArrange);
            }
            else
            {
                salesParmTableReArrange.clear();
                this.createParmTable(salesTable, null, salesSummary.summaryTableRef(), true);
            }
        }

        moveParmLine(localSalesParmSubTable.OrigSalesId, localSalesParmSubTable.TableRefId,
                     salesSummary.summarySalesId(), salesSummary.summaryTableRef());

        localSalesParmSubTable.TableRefId   = salesSummary.summaryTableRef();
    }
    //-------------------------------------------------
    // this code will be executed in case of intercompany and summary update =
    // "Invoice account. There will be no summary
    //---------------------------------------------------
    else
    {
        ...

This is also true for AX2009 (\Classes\SalesFormLetter\reArrange, line 242)

We haven’t posted any request to Microsoft so far, but we have corrected the “if” statement so that the orders, posted from our customization are also summarized.

I consider the issue to be in place also for normal intercompany purchase (SO 101 - PO 102). It has nothing to do with the direct delivery.

I haven’t found any documentation, stating, that intercompany packing slips cannot be summarized by account, so I suppose it's a bug.