Skip to Content

SQL table to query the HCM staging area identity store

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 17, 2014 at 05:43 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 17, 2014 at 05:27 AM

    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

    Add comment
    10|10000 characters needed characters exceeded