SQL queries to get customers and vendor addresses and contact details in D365 F&O/ AX X++

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

Get Customer Address By Type In D365/AX X++

Below is the piece of code to get customer address by type in AX/D365 X++

public static LogisticsPostalAddress getPostalAddressByType(DirPartyRecId _party, LogisticsLocationRoleType _type)

{

    DirPartyLocation        partyLocation;

    DirPartyLocationRole    partyLocationRole;

    LogisticsLocation       location;

    LogisticsLocationRole   locationRole;

    LogisticsPostalAddress  postalAddress;

    select firstonly postalAddress

        exists join location

            where location.RecId == postalAddress.Location

        exists join locationRole

            where locationRole.Type  == _type

        exists join partyLocation

            where 

                partyLocation.Location == location.RecId &&

                partyLocation.Party == _party

        exists join partyLocationRole

            where partyLocationRole.PartyLocation == partyLocation.RecId &&

                partyLocationRole.LocationRole == locationRole.RecId;

    return postalAddress;

}

How to hide New and Delete buttons in a form in Dynamics AX/ D365 F&O X++

Below is the simple code I have written in form init() method to hide new and delete button from the user interface.

    public void init()
    {
        this.form().design().showNewButton(0);
        this.form().design().showDeleteButton(0);
        super();

    }

or

[ExtensionOf(FormStr(InventBatch))]
public final class P_InventBatchForm_Extension
{
    void run()
    {
        next run();
        inventBatch_ds.allowCreate(false); // Disable System Defined NEW Button
      }

}

Let me know if you have any questions

@Rahul