SQL Query To Return Employee Names And Their Phone Numbers

(0) Share

Report

Posted on by Community Member

I wrote the beloq SQL query to get the phone numbers of all current employees in AX2012. It seems to return most but not all. I also see there's a few records showing details of former employees.

SELECT	a.PERSONNELNUMBER AS [Personnel Number],
							c.FIRSTNAME AS [First Name],
							c.LASTNAME AS [Last Name],
							b.LOCATOR AS [Phone Number],
							b.DESCRIPTION AS [Number Type]
					FROM		HCMWORKER AS a INNER JOIN	
								DIRPERSONNAME AS c ON a.PERSON = c.PERSON 
					LEFT OUTER JOIN	LOGISTICSELECTRONICADDRESS AS b ON c.PERSON = b.PRIVATEFORPARTY
										WHERE CAST(c.VALIDTO AS DATE) = '21541231'

I think the issue comes from an incorrect link between the HCMWORKER and LOGISTICSELECTRONICSADDRESS.

Can anybody please advise the correct way to query this information?

Thanks in advance.

Paul

*This post is locked for comments

I have the same question (0)

All responses (1)

Answers (0)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: SQL Query To Return Employee Names And Their Phone Numbers

Hi runnerpaul,

You can try to use "DirPartyContactInfoView" view instead of LOGISTICSELECTRONICADDRESS.

Try join them next way :

FROM HCMWORKER AS a INNER JOIN

DIRPERSONNAME AS c

ON a.PERSON = c.PERSON

LEFT OUTER JOIN DirPartyContactInfoView AS b

ON c.PERSON = b.PARTY

WHERE CAST(c.VALIDTO AS DATE) = '21541231'

Was this reply helpful? Yes No