D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Technical issue - custinvoicetrans table - AX 2012 R2

  • 1.  Technical issue - custinvoicetrans table - AX 2012 R2

    TOP CONTRIBUTOR
    Posted Mar 29, 2019 01:09 PM
    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.

    address no longer effective

    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
    ------------------------------


  • 2.  RE: Technical issue - custinvoicetrans table - AX 2012 R2

    Posted Apr 01, 2019 01:25 AM
    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
    ------------------------------



  • 3.  RE: Technical issue - custinvoicetrans table - AX 2012 R2

    TOP CONTRIBUTOR
    Posted Apr 01, 2019 10:29 AM
    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
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role