AX / D365FO – SQL queries to get customers and vendor addresses and contact details

Below are the sample queries to get the vendors and customers addresses and contact details. Change the code according to your requirement.

All Vendors
SELECT * FROM VENDTABLE WHERE VENDTABLE.DATAAREAID=’CEU’

All Addresses – Vendor 
SELECT * FROM  DirPartyPostalAddressView  JOIN VENDTABLE ON  DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY
WHERE VENDTABLE.DATAAREAID=’XXX’

All Addresses with Purpose
SELECT LOGISTICSLOCATIONROLE.*,DirPartyPostalAddressView.*,VENDTABLE.* FROM  DirPartyPostalAddressView  JOIN VENDTABLE ON  DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY
JOIN DIRPARTYLOCATIONROLE ON  DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID
JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID
WHERE VENDTABLE.DATAAREAID=’XXX’
(Click Organization administration > Setup > Global address book > Address and contact information purpose.)

All Contact Details – Vendor 
select * from dirPartyContactInfoView JOIN VENDTABLE ON  dirPartyContactInfoView.PARTY =VENDTABLE.PARTY
WHERE VENDTABLE.DATAAREAID=’XXX’

–All Customers
–SELECT DIRPARTYTABLE.NAMEALIAS ,CUSTTABLE.* FROM CUSTTABLE JOIN DIRPARTYTABLE ON  CUSTTABLE.PARTY =DIRPARTYTABLE.RECID
–WHERE CUSTTABLE.DATAAREAID=’XXX’

–All Addresses – Customer 
–SELECT DirPartyPostalAddressView.*,CUSTTABLE.PARTY  FROM  DirPartyPostalAddressView  JOIN CUSTTABLE
–ON  DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY
–WHERE CUSTTABLE.DATAAREAID=’XXX’

–All Addresses with Purpose – Customer
–SELECT LOGISTICSLOCATIONROLE.NAME,DirPartyPostalAddressView.PARTY,CUSTTABLE.PARTY FROM  DirPartyPostalAddressView  JOIN CUSTTABLE
–ON  DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY
–JOIN DIRPARTYLOCATIONROLE ON  DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID
–JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID
–WHERE CUSTTABLE.DATAAREAID=’XXX’

–All Contact Details – Customer 
–select dirPartyContactInfoView.* from dirPartyContactInfoView JOIN CUSTTABLE ON  dirPartyContactInfoView.PARTY =CUSTTABLE.PARTY
–WHERE CUSTTABLE.DATAAREAID=’XXX’

–Bank Details Customer
 –SELECT distinct CUSTTABLE.PARTY ,CUSTTABLE.DATAAREAID ENTITY, CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME,Address.ADDRESS ,
 –CUSTTABLE.CURRENCY ,CUSTTABLE.CUSTGROUP  ,
 –CUSTTABLE.PAYMTERMID,CUSTTABLE.TAXGROUP VATGROUP ,CUSTTABLE.CASHDISC ,
 –VendBankAccount.ACCOUNTID BankAccount ,VendBankAccount.NAME ‘Bank Name’ ,VendBankAccount.ACCOUNTNUM ‘Bank account number’,
 –VendBankAccount.RegistrationNum ‘Routing Number’,VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN
 –from CUSTTABLE left outer JOIN VendBankAccount ON VendBankAccount.VENDACCOUNT = CUSTTABLE.ACCOUNTNUM –AND  VendBankAccount.ACCOUNTID = CUSTTABLE.BANKACCOUNT
 –left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY
 –left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
 — WHERE CUSTTABLE.DATAAREAID IN (‘XXX’) –and CUSTTABLE.ACCOUNTNUM =’test033′
 –Order by  CUSTTABLE.DATAAREAID,CUSTTABLE.ACCOUNTNUM