SQL queries to get customers and vendor addresses and contact details in D365 F&O/ AX X++
Rahul Kiran
481
Share
ReportBelow 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=''CEU'
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='CEU'
(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='CEU'
--All Customers
--SELECT DIRPARTYTABLE.NAMEALIAS ,CUSTTABLE.* FROM CUSTTABLE JOIN DIRPARTYTABLE ON CUSTTABLE.PARTY =DIRPARTYTABLE.RECID
--WHERE CUSTTABLE.DATAAREAID='CEU'
--All Addresses - Customer
--SELECT DirPartyPostalAddressView.*,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE
--ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY
--WHERE CUSTTABLE.DATAAREAID='CEU'
--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='CEU'
--All Contact Details - Customer
--select dirPartyContactInfoView.* from dirPartyContactInfoView JOIN CUSTTABLE ON dirPartyContactInfoView.PARTY =CUSTTABLE.PARTY
--WHERE CUSTTABLE.DATAAREAID='CEU'
--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 ('CEU') --and CUSTTABLE.ACCOUNTNUM ='test033'
--Order by CUSTTABLE.DATAAREAID,CUSTTABLE.ACCOUNTNUM
Below SQL Query to extract a quick customer contact list from Dynamics AX 2012.
REF
REF
SELECT
VENDTABLE.ACCOUNTNUM AS CUSTID,
DIRPARTYTABLE.NAME AS CUSTNAME,
CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,
LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME,
LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS
FROM DIRPARTYTABLE AS DIRPARTYTABLE
INNER JOIN VENDTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
INNER JOIN DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY
INNER JOIN LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION
WHERE VENDTABLE.DATAAREAID='CEU'
ORDER BY DIRPARTYTABLE.NAME
Vendor Bank Details SQL Query in AX
Vendor Bank Address in X++ Code and save it in CSV in AX
Fetch Customers Primary Address or Vendor Primary Address having Transactions (SQL SERVER Query) in AX D365
Fetch Product Master
SELECT B.DISPLAYPRODUCTNUMBER,DESCRIPTION,NAME,B.SEARCHNAME FROM ECORESPRODUCTTRANSLATION A JOIN ECORESPRODUCT B ON A.PRODUCT =B.RECID WHERE B.DISPLAYPRODUCTNUMBER in ('A0001','A0002')
--Storage Dimensions
select ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID ,ECORESSTORAGEDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESSTORAGEDIMENSIONGROUPITEM ON
INVENTTABLE.ITEMID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID AND
INVENTTABLE.DATAAREAID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMDATAAREAID
JOIN ECORESSTORAGEDIMENSIONGROUP ON
ECORESSTORAGEDIMENSIONGROUPITEM.STORAGEDIMENSIONGROUP =ECORESSTORAGEDIMENSIONGROUP.RECID
WHERE INVENTTABLE.DATAAREAID='CEU'
--Tracking Dimensions
select ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID ,ECORESTRACKINGDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESTRACKINGDIMENSIONGROUPITEM ON
INVENTTABLE.ITEMID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID AND
INVENTTABLE.DATAAREAID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMDATAAREAID
JOIN ECORESTRACKINGDIMENSIONGROUP ON
ECORESTRACKINGDIMENSIONGROUPITEM.TRACKINGDIMENSIONGROUP =ECORESTRACKINGDIMENSIONGROUP.RECID
WHERE INVENTTABLE.DATAAREAID='CEU'
--Item Model Group Units
select * from INVENTTABLEMODULE where DATAAREAID='CEU'
--Released Products
Select * from INVENTTABLE where DATAAREAID='CEU'
--Item Group
select * from INVENTITEMGROUPITEM where ItemDATAAREAID='CEU'
Vend Open Trans
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount
FROM VENDTRANS
where VENDTRANS.closed = 0
AND ((VendTrans.TransType = 36)
OR (VendTrans.TransType = 3) --Purch
OR (VendTrans.TransType = 14)) --Vend
AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount ,*
FROM VENDTRANS where
--where VENDTRANS.closed = 0
--AND ((VendTrans.TransType = 36) OR (VendTrans.TransType = 3)
-- OR (VendTrans.TransType = 14)) AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
VENDTRANS.DATAAREAID = 'pui'
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount
FROM VENDTRANS
where VENDTRANS.closed = 0 and
VENDTRANS.DATAAREAID = 'pui'
AND ((VendTrans.TransType = 36) --RTax25_BadDebtDebitAmortisation
OR (VendTrans.TransType = 3) --Purch
OR (VendTrans.TransType = 14)) --Vend
AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'
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=''CEU'
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='CEU'
(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='CEU'
--All Customers
--SELECT DIRPARTYTABLE.NAMEALIAS ,CUSTTABLE.* FROM CUSTTABLE JOIN DIRPARTYTABLE ON CUSTTABLE.PARTY =DIRPARTYTABLE.RECID
--WHERE CUSTTABLE.DATAAREAID='CEU'
--All Addresses - Customer
--SELECT DirPartyPostalAddressView.*,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE
--ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY
--WHERE CUSTTABLE.DATAAREAID='CEU'
--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='CEU'
--All Contact Details - Customer
--select dirPartyContactInfoView.* from dirPartyContactInfoView JOIN CUSTTABLE ON dirPartyContactInfoView.PARTY =CUSTTABLE.PARTY
--WHERE CUSTTABLE.DATAAREAID='CEU'
--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 ('CEU') --and CUSTTABLE.ACCOUNTNUM ='test033'
--Order by CUSTTABLE.DATAAREAID,CUSTTABLE.ACCOUNTNUM
Below SQL Query to extract a quick customer contact list from Dynamics AX 2012.
REF
REF
SELECT
VENDTABLE.ACCOUNTNUM AS CUSTID,
DIRPARTYTABLE.NAME AS CUSTNAME,
CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,
LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME,
LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS
FROM DIRPARTYTABLE AS DIRPARTYTABLE
INNER JOIN VENDTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
INNER JOIN DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY
INNER JOIN LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION
WHERE VENDTABLE.DATAAREAID='CEU'
ORDER BY DIRPARTYTABLE.NAME
Vendor Bank Details SQL Query in AX
Vendor Bank Address in X++ Code and save it in CSV in AX
Fetch Customers Primary Address or Vendor Primary Address having Transactions (SQL SERVER Query) in AX D365
Fetch Product Master
SELECT B.DISPLAYPRODUCTNUMBER,DESCRIPTION,NAME,B.SEARCHNAME FROM ECORESPRODUCTTRANSLATION A JOIN ECORESPRODUCT B ON A.PRODUCT =B.RECID WHERE B.DISPLAYPRODUCTNUMBER in ('A0001','A0002')
--Storage Dimensions
select ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID ,ECORESSTORAGEDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESSTORAGEDIMENSIONGROUPITEM ON
INVENTTABLE.ITEMID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID AND
INVENTTABLE.DATAAREAID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMDATAAREAID
JOIN ECORESSTORAGEDIMENSIONGROUP ON
ECORESSTORAGEDIMENSIONGROUPITEM.STORAGEDIMENSIONGROUP =ECORESSTORAGEDIMENSIONGROUP.RECID
WHERE INVENTTABLE.DATAAREAID='CEU'
--Tracking Dimensions
select ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID ,ECORESTRACKINGDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESTRACKINGDIMENSIONGROUPITEM ON
INVENTTABLE.ITEMID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID AND
INVENTTABLE.DATAAREAID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMDATAAREAID
JOIN ECORESTRACKINGDIMENSIONGROUP ON
ECORESTRACKINGDIMENSIONGROUPITEM.TRACKINGDIMENSIONGROUP =ECORESTRACKINGDIMENSIONGROUP.RECID
WHERE INVENTTABLE.DATAAREAID='CEU'
--Item Model Group Units
select * from INVENTTABLEMODULE where DATAAREAID='CEU'
--Released Products
Select * from INVENTTABLE where DATAAREAID='CEU'
--Item Group
select * from INVENTITEMGROUPITEM where ItemDATAAREAID='CEU'
Vend Open Trans
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount
FROM VENDTRANS
where VENDTRANS.closed = 0
AND ((VendTrans.TransType = 36)
OR (VendTrans.TransType = 3) --Purch
OR (VendTrans.TransType = 14)) --Vend
AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount ,*
FROM VENDTRANS where
--where VENDTRANS.closed = 0
--AND ((VendTrans.TransType = 36) OR (VendTrans.TransType = 3)
-- OR (VendTrans.TransType = 14)) AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
VENDTRANS.DATAAREAID = 'pui'
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount
FROM VENDTRANS
where VENDTRANS.closed = 0 and
VENDTRANS.DATAAREAID = 'pui'
AND ((VendTrans.TransType = 36) --RTax25_BadDebtDebitAmortisation
OR (VendTrans.TransType = 3) --Purch
OR (VendTrans.TransType = 14)) --Vend
AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'
This was originally posted here.
*This post is locked for comments