Query to get all the Primary address details of an Vendor in Ax/D365

 

The following simple Sql query is used to retrieve the all primary details like, Address, Tax information of a Vendor.

SELECT vendtable.accountnum,

       dirpartytable.NAME,

       vendtable.vendgroup,

LogisticsLocationRole.NAME as Purpose,

   LogisticsPostalAddress.CITY,

   LogisticsPostalAddress.STATE,

   LogisticsPostalAddress.ZIPCODE,

  LogisticsPostalAddress.STREET,

  LogisticsPostalAddress.COUNTRYREGIONID,

   LogisticsPostalAddress.ADDRESS,

   TAXINFORMATION_IN.NAME,

       ISNULL(taxinformationvendtable_in.pannumber, '') AS [PANNUMBER],

       ISNULL(taxregistrationnumbers_in.registrationnumber, '') AS [GST IN],

       ISNULL(TaxWithholdRegNumbers_IN.registrationnumber, '') AS [TAN]

FROM vendtable

LEFT JOIN dirpartytable ON dirpartytable.recid = vendtable.party

LEFT JOIN logisticslocation ON logisticslocation.recid = dirpartytable.primaryaddresslocation

Left Join DIRPARTYLOCATION on DIRPARTYLOCATION.LOCATION =LOGISTICSLOCATION.RECID

Left join DirPartyLocationRole  on DirPartyLocationRole.PARTYLOCATION = DIRPARTYLOCATION.RECID

Left Join LogisticsLocationRole on LogisticsLocationRole.RECID = DirPartyLocationRole.LOCATIONROLE 

Left Join LogisticsPostalAddress on LogisticsPostalAddress.LOCATION = logisticslocation.recid

and LogisticsPostalAddress.VALIDTO > GETUTCDATE()

LEFT JOIN taxinformation_in ON taxinformation_in.registrationlocation = logisticslocation.recid

AND taxinformation_in.isprimary = 1

LEFT JOIN taxregistrationnumbers_in ON taxregistrationnumbers_in.recid = taxinformation_in.gstin

Left Join TaxWithholdRegNumbers_IN on TaxWithholdRegNumbers_IN.RECID  = taxinformation_in.TAN

LEFT JOIN taxinformationvendtable_in ON taxinformationvendtable_in.vendtable = vendtable.accountnum

AND taxinformationvendtable_in.dataareaid = vendtable.dataareaid

--WHERE vendtable.dataareaid = 'AP'

Same thing you can Apply for a customer also.

Thanks !!