Solved: Link ProjTransPosting RESOURCE

Link ProjTransPosting RESOURCE_ to Employee name

Hi everyone,

I try to find employee names related to who wrote hours (ProjTransPosting).

I thought it was as simple as this, but in our case we don't get the correct names:

SELECT DISTINCT
   RESOURCE_
   ,PT.ProjId
   ,PT.CategoryId
,CONCAT_WS(' ',DPN.FirstName, DPN.LastNamePrefix, DPN.LastName) AS [Naam]
FROM [ODS].[D365FO_DATALAKE_PROJTRANSPOSTING] PT
LEFT JOIN ODS.D365FO_DATALAKE_HCMWORKER HW
   ON HW.RECID = PT.RESOURCE_
LEFT JOIN ODS.D365FO_DATALAKE_DIRPERSONNAME DPN --incorrect names
   ON DPN.Person = HW.Person

LEFT JOIN ODS.D365FO_DATALAKE_DIRPARTYTABLE DP --this join doesn't work either
ON DP.RECID = PT.RESOURCE_

The only other field in ProjTransPosting table that seems to be related to employees is EmplItemID, but I don't see any value there that corresponds even slightly with what I find in HcmWorker or DirPerson. We do not have a field called 'Worker' or anything similar.

What I don't understand is that the employee names I am looking for are visible in 'Rol-id' column, which doesn't really sound like an employee field to me either.

I am talking here about form 'ProjTransEmpl' or entry point 'ProjEmplTrans_Resource_name'.

What is actually the technical name from the table where I can find these 'Rol-id' values and how can I connect this to the ProjTransPosting table?

Any suggestions are very welcome :)

Kind regards,

Igor

Quick Links

Leaderboard