importing customer vendor addresses with roles from AX to D365FO Microsoft Dynamics 365 Finance and Operations
in many data migration projects we need to migrate addresses and one of common problem is getting single address with multiple roles imported in D365FO in a simple way.
sharing one such of my learning for SQL 2017+ and SQL 2016- versions:
SELECT VT.ACCOUNTNUM as VENDORACCOUNTNUMBER, VT.DATAAREAID as VENDORLEGALENTITYID,
--SQL 2017 (select STRING_AGG(LOGISTICSLOCATIONROLE.NAME, ';') from LOGISTICSLOCATIONROLE where DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID) AS ADDRESSLOCATIONROLES,
STUFF(( SELECT ';' + LOGISTICSLOCATIONROLE.NAME FROM LOGISTICSLOCATIONROLE
LEFT JOIN DIRPARTYLOCATIONROLE ON DIRPARTYLOCATIONROLE.PARTYLOCATION = DPA.RECID
WHERE DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID
FOR XML PATH('')), 1, LEN(','), '') AS ADDRESSLOCATIONROLES,
DPA.LOCATIONNAME as ADDRESSDESCRIPTION,
DPA.STREET as ADDRESSSTREET,
DPA.CITY as ADDRESSCITY,
DPA.STATE as ADDRESSSTATEID,
DPA.COUNTRYREGIONID AS ADDRESSCOUNTRYREGIONID,
DPA.COUNTY as ADDRESSCOUNTYID,
DPA.DISTRICT as ADDRESSDISTRICTNAME,
DPA.ZIPCODE as ADDRESSZIPCODE,
DPA.RECID
FROM DirPartyPostalAddressView as DPA
JOIN VENDTABLE VT ON DPA.PARTY = VT.PARTY
WHERE VT.DATAAREAID='USMF'
and DPA.VALIDTO='2154-12-31 23:59:59.000'
*This post is locked for comments