Retrieve Primary Address state of customer

Question

I am trying to retrieve the state through SQL Server that is filled in on the primary address of a customer.

First I was trying to use the following query:

DATABASE.dbo.LOGISTICSPOSTALADDRESS AS LPA ON DATABASE.dbo.DIRPARTYTABLE.RECID = LPA.PRIVATEFORPARTY LEFT OUTER JOIN
DATABASE.dbo.LOGISTICSADDRESSSTATE ON DATABASE.dbo.LOGISTICSADDRESSSTATE.STATEID = LPA.STATE

After that I have been trying to join LogisticsLocation on the DirPartyTable, and then join LogisticsLocation on LogisticsPostalAddress. This gives me results, but double records with different states. I need just 1 result for the primary address. How can I make this happen?

Answer 1

Here you go. Change the customer account obviously. This gets the state of the primary address, but it also has the entire primary address if you need that for some reason.

What is important to note is the LogisticsPostalAddress table is a "Valid Time State" table. This means the records have a from/to that they are valid for.

The reason you would want this is if you have a customer at 123 South St and you create a sales order and send him an invoice with that address printed on it, then you later change the customer primary address to 654 North St. Well if they asked for a re-print of their old invoice, you want to ensure that previous address still exists.

One thing to note with my queries is I haven't checked if timezone is considered correctly between the GetUTCDate() SQL function and the way AX stores the date/time. This would be a minor difference but something to consider.

Here is with a view:

SELECT a.STATE
FROM DIRPARTYPOSTALADDRESSVIEW AS a
WHERE a.ISPRIMARY = 1
    AND a.VALIDTO > GETUTCDATE()
    AND EXISTS (
        SELECT PARTY
        FROM CUSTTABLE AS b
        WHERE b.PARTY = a.PARTY
            AND b.ACCOUNTNUM = 'AAATest' /*Change This*/
        )

Here is using some of the tables:

SELECT d.STATE
FROM CUSTTABLE a
JOIN dirpartytable b ON b.RECID = a.PARTY
JOIN LOGISTICSLOCATION c ON c.RECID = b.PRIMARYADDRESSLOCATION
JOIN LOGISTICSPOSTALADDRESS d ON d.LOCATION = c.RECID
WHERE d.VALIDTO > GETUTCDATE()
    AND a.ACCOUNTNUM = 'AAATest' /*Change This*/