SQL table to query the HCM staging area identity store

[former_member297605]

Hi

I'm pretty new to IDM and need help please.

I'm trying to locate which table view in SQL would give me the manager details brought from HCM into the HCM staging area identity store.

I was advised that this information would be in the SAP_MASTER IS but I'm confused as I only have 2 IDs - one productive & the other the HCM staging area

Please could you give me some info on this.

IDM 7.2 SP9

Database - SQL

Thanks

Ran

[terovirta · Accepted solution]

The manager in IdM is a reference between two IdM records, the mx_manager in SAP Master Id Store would contain MSKEY of the manager record.

HCM doesn't know what is the MSKEY of manager in IdM (as MSKEY is autonumber) so you get the Personnel Number of Manager to Staging Id Store in order for you to build the reference between the manager and subordinate records in IdM.

regards, Tero

[former_member297605 · Accepted solution]

Thanks Tero..... much appreciated. I'll try this and get back to you. Thanks a lot

[former_member297605 · Accepted solution]

Thanks Tero that worked perfectly.

[terovirta]

Hello,

you'll find the data from view "idmv_value_basic", the Id Stores are separated by the Id Store Id which is column IS_ID.

Basically querying as follows should return the full contents of Staging Id Store:

select mskey, AttrName, SearchValue, Modifytime, IS_ID

from idmv_value_basic

where IS_ID = [STAGING_ID_STORE_ID]

order by mskey, attrName

You'll find the Staging Id Store Id from MMC in the Id Store properties.

The manager-attribute is stored as "Personnel Number of Manager" in Staging Id Store, if you have followed the SAP documentation in naming the attributes that the LDAP-functionality in HCM the attribute should be P0001-SYHR_A_P0001_AF_OMNGR_NR.

You would first need to query for the user in Staging Id Store with username or personnel number and once you find the mskey for that user in Staging then you can query for the Manager's Personnel Number..

User's personnel number:

select mskey, attrname, searchvalue from idmv_value_basic where attrname = 'P0000-PERNR' and searchvalue like '%[USERS_PERSONNEL_NUMBER]'

Once you got the user's MSKEY in staging:

select mskey, attrname, searchvalue from idmv_value_basic where mskey = [USERS_MSKEY_IN_STAGING] order by AttrName

regards, Tero