Fetch Customers Address or Vendor Address having Transactions (SQL SERVER Query) in AX D365

Hi,

Fetch Customers Address or Vendor Address having Transactions (SQL SERVER Query) in AX D365

REFERENCE

### All Supplier Addresss Reference

 select VENDTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS
 from VENDTABLE
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID
 where DATAAREAID='test'
 Order by VENDTABLE.ACCOUNTNUM

Fetch Vendor Address having Transactions

### Supplier has transaction for last 12 month (VendTrans)

 select distinct VENDTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS
 from VENDTABLE
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID
 inner join VENDTRANS a on a.ACCOUNTNUM =VENDTABLE.ACCOUNTNUM
 where VENDTABLE.DATAAREAID='test' and a.TRANSDATE between '2016-09-25' and '2017-09-27'
 --and year(a.TRANSDATE) >='2016'
 Order by VENDTABLE.ACCOUNTNUM


Fetch Vendor Address with no Transactions

Reference

/Get Customer/Vendor Address by Id & Current Comapny Address

//By replacing the CustTable with the VendTable we will get the address of Vendor

static void Job85(Args _args)

{

    CustTable custTable;

    str       description,address,completeaddress;

     

    custTable = CustTable::find('L0115');

           

    description = DirParty::primaryPostalAddress(custTable.Party).displayLocationDescription();

     

    address = DirParty::primaryPostalAddress(custTable.Party).Address;

     

    completeaddress = strFmt("%1,%2",description,address);

    info(strFmt("%1",completeaddress));

     

    //Get the Current Company Address

    info(strFmt("%1,%2",curext(),LogisticsPostalAddress::findByLocation(CompanyInfo::find().postalAddress().Location).Address));

     

}

REF