Has anyone been able to access Employee Identification Numbers through a SQL query?

I am trying to extract employee information out of GHR using the SQL node but so far I am unable to get their Identification Number. I know I can access it through the Landmark Transaction node but don't really want to add the extra process if I do not have to. Has anyone been able to extract the Identification Numbers using the SQL node?

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

unknown

If you are trying to get their SSN in landmark, then the S3 field is invalid and you cannot get their SSN via SQL and have to use a landmark query node and pass their employeeid to it to return the SSN.

unknown

The landmark query you can use is:

_dataArea="<!dataarea>" & _module="hr" & _objectName="EmployeeIdentificationNumber" & _actionName="Find" & _actionOperator="NONE" & _actionType="SingleRecordQuery" & _runAsUser="" & _authenticatedUser="" & _pageSize="30" & _relationName="" & _setName="SymbolicKey" & _asOfDate="" & _effectiveDate="" & HROrganization="<!hrorg>" & Employee="<!employeeid>" & EmployeeIdentificationNumber="1" & IdentificationNumberDisplay

Otherwise in the S3.Employee table the field is [FICA_NBR] for their SSN but usually its encrypted in landmark.

All comments

andy

Are you single tenant or multi-tenant?

I believe it's just the EMPLOYEE in the [LMGHRDB].[lm_ghr].[EMPLOYEE] table.

jreese

I am on premise and it isn't the Employee Number that I need it is the Identification Number which is their SSN.

Gelangens_Anlage_Entwurf.pdf

unknown

If you are talking about their SSN, it is in GHR it is in the EMPLOYEEIDENTIFICATION table. Unfortunately, you won't see the whole SSN. Only 4 numbers are kept and they are every other number of the SSN.

tammi-fedko

The whole SSN is stored in the EMPLOYEEIDENTIFICATION business class in the field called Identification Number, however this field is secured and cannot be access via SQL, you'll have to use the landmark transaction node to see this data.

The Release notes for Talent Management provides a document called TMEncrypted Tables and it lists out the business classes and fields where the data is encrypted and cannot be accessed via SQL.

jreese

Thanks Tammi. That's what I figured but like a lot of things in Landmark makes no sense. Not everyone has IPA so makes you wonder how they are supposed to access the data without manual intervention.

Jim Norbut

You can get at it via SpreadSheet Designer I believe (if that's a viable option)

jreese

I need to get it through an automated process. I understand encrypting data for security purposes but you should have some way to pass a security key to allow you to access your own data especially if you are not in the cloud. I can get it through IPA with the transaction node but it seems ridiculous to have to add an additional step to get one piece of data. At least provide access to the last 4 digits instead of a scrambled worthless number in the PartofIdentificationNumber field.

unknown

You may want to verify that 4 digit number. We found it was not the last 4 digits. We discovered it was every other digit from the full SSN.

0911060553270289.pdf

unknown

The other way I've seen before is that DBAs created a combined replication database for both S3 and GHR. And so the SSN is coming from S3.

You can try to create another field to house the Identification number, say just the last four, but you would probably need to ask IT if they will allow a nonencrypted SSN.

unknown

Copyright © 2025 Infor. All rights reserved.