Get worker name from position with SQL Queries

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
    (select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
    INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
    AND HCMPOSITION.POSITIONID = 'PositionId'
    where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM 
    AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

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

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'