SQL/report to get the list of consultants (not having Person ID)

Vijay Ramani VJ

We are looking for a user/role report that list users firstname/lastname/email and roles. The report we have only lists if they are linked to a person record.

We have consultants who doesn't have person ID associated with their username. We need to get a list of those users (firstname/lastname/email).

Anyone has a sql or report how it was created?

Thanks

Vj

Meredith Bickell

https://docs.oracle.com/en/cloud/saas/human-resources/22c/oedmh/perusers-18878.html#perusers-18878

select * from per_users where person_id is null

Would they have a PARTY_ID?

https://docs.oracle.com/en/cloud/saas/sales/22c/oedms/hzparties-7234.html#hzparties-7234

select * from hz_parties where party_id in (select party_id from per_users where person_id is null)

Vijay Ramani VJ

Hi Meredith,

How do you get the email of the users where there is no person id?

Meredith Bickell

Looking at the hz_parties table at https://docs.oracle.com/en/cloud/saas/sales/22c/oedms/hzparties-7234.html#hzparties-7234 there is an email_adress field.

Vijay Ramani VJ

looks like it is not possible. This is what I found from support.

For Implementation users, the email address is directly stored in OID. it's not stored in Fusion DB.

Only when you associate an employee record with that Implementation user, you will be able to fetch the email address of such users(Per_email_address).