D365 Finance & Operations and Dynamics AX Forum

Hello All,

I wrote a report in Atlas using the custinvoicetrans table that included state and country.  The report in AX that I always compare it against for accuracy in the Gross Margin by Item report,

Recently, someone noticed that the numbers were off quite a bit.

I've gone through several lines on the gross margin report vs my Atlas report.  For every invoice that was not on my report, the sales order had the error message below in yellow.

It appears to happen if an address gets updated or removed.  Sometimes the delivery address is different on the header and the lines.  I don't know if that has anything to do with it, but it was an observation.

So, now that I have tracked down the problem, I have no idea at this moment how to make report find an address that is no longer there.

Anyway, is there another table that would be better for me to use that holds the original invoice information?  Or a method to find all the sales orders where the address is no longer effective so we can fix them?

Thanks in advance!

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------

Hi Sam

Unfortunately I do not use R2 so I can only try to provide some generic help.

Did you already check if those items are really not in your table in AX?

Sometimes I only miss some entries due to the filters I set or because I merge tables and this does not pull all the lines as a result anymore. (Did you use any merge or join functionality in ATLAS?)

Let me know if that is already the root cause otherwise I will try to regenerate the issue in R3 and send you some feedback on the results.

Cheers 

Carsten


------------------------------
Carsten Schoenberg
FITPRO SOLUTIONS
Hamburg
------------------------------

Original Message:
Sent: Mar 29, 2019 01:08 PM
From: Sam Clark
Subject: Technical issue - custinvoicetrans table - AX 2012 R2

Hello All,

I wrote a report in Atlas using the custinvoicetrans table that included state and country.  The report in AX that I always compare it against for accuracy in the Gross Margin by Item report,

Recently, someone noticed that the numbers were off quite a bit.

I've gone through several lines on the gross margin report vs my Atlas report.  For every invoice that was not on my report, the sales order had the error message below in yellow.

It appears to happen if an address gets updated or removed.  Sometimes the delivery address is different on the header and the lines.  I don't know if that has anything to do with it, but it was an observation.

So, now that I have tracked down the problem, I have no idea at this moment how to make report find an address that is no longer there.

Anyway, is there another table that would be better for me to use that holds the original invoice information?  Or a method to find all the sales orders where the address is no longer effective so we can fix them?

Thanks in advance!

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------

Sam,

How addresses are treated within AX is not very intuitive, they are stored in the LogisticsPostalAddress table and once an address is created it is never deleted. On each record in this table there is a ValidTo and ValidFrom field, this is a datetime field that allows the system to determine which address is valid for a particular location while also keeping a change history of addresses.

So when you create an address here the ValidFrom date is set to the minimum date time (01-01-1900) and the ValidTo date is set to the maximum datetime (12-31-2154). When an address is updated the old address' ValidTo date is set to the current date time and the updated address' ValidFrom date is set to the current date time. An example of this is shown below:


My guess from the warning you are getting is that the location address for the customer invoice has been updated but is not being reflected by the invoice itself, which means that it is using an address where the current date is not between the ValidFrom and ValidTo fields of the address.

If you wanted to write a SQL query to find customer invoices like this you could do something like this:

  select
  ci.INVOICEACCOUNT [InvoiceAccount],
  lpa.[ADDRESS] [CustomerInvoiceAccountAddress],
  lpa2.[ADDRESS] [CurrentLocationAddress]
  from CustInvoiceTable ci
  join LOGISTICSPOSTALADDRESS lpa
	on ci.DELIVERYLOCATION = lpa.RECID
  join LOGISTICSPOSTALADDRESS lpa2
	on lpa2.[LOCATION] = lpa.[LOCATION] and lpa2.VALIDTO = '2154-12-31 23:59:59.000'
  where lpa.VALIDTO < CURRENT_TIMESTAMP​

------------------------------
Alex Meyer
Director of Dynamics AX/365 for Finance & Operations Development
Fastpath
Des Moines, IA
------------------------------

Original Message:
Sent: Mar 29, 2019 01:08 PM
From: Sam Clark
Subject: Technical issue - custinvoicetrans table - AX 2012 R2

Hello All,

I wrote a report in Atlas using the custinvoicetrans table that included state and country.  The report in AX that I always compare it against for accuracy in the Gross Margin by Item report,

Recently, someone noticed that the numbers were off quite a bit.

I've gone through several lines on the gross margin report vs my Atlas report.  For every invoice that was not on my report, the sales order had the error message below in yellow.

It appears to happen if an address gets updated or removed.  Sometimes the delivery address is different on the header and the lines.  I don't know if that has anything to do with it, but it was an observation.

So, now that I have tracked down the problem, I have no idea at this moment how to make report find an address that is no longer there.

Anyway, is there another table that would be better for me to use that holds the original invoice information?  Or a method to find all the sales orders where the address is no longer effective so we can fix them?

Thanks in advance!

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------