Customer invoice line amount measure multiple records

The current release of AX 2012 R3  has a scenario where the Sales cube measure ‘ Customer invoice line amount – account currency’ displays the incorrect amount.  Compare this to the Sales cube measure ‘Customer invoice amount – accounting currency’ and where the balance should be the same they are different.

The issue is in the relationships between the CustInvoiceTrans and CustInvoiceJour, it is possible that there is no unique relationship between these tables if the same invoice number is used when posting journals.

CustInvoiceTransQuery

Take this scenario

1. User posts a customer payment with the incorrect amount using invoice number ’02’

2. User reverses payment and uses the same invoice number ’02’

3. User enters the correct amount and uses the same invoice number ’02’

When opening the AOT View CustInvoiceTransExpanded in AX you can see the multiple records.

Duplicates

This View is used in the SalesCube and therefore the incorrect balance is displayed.

I have not been able to solve this as there is no unique join that can be used but as a work around when creating your own reports is to use a different measure in the Sales cube ‘Customer invoice amount – accounting currency’ this gives the correct balance.

Workaround

I’ll update this post if Microsoft offer a solution.

Leave a Reply

Your email address will not be published. Required fields are marked *