• State Suggested Answer
  • +1 person also asked this people also asked this
  • Locked Locked
  • Replies 8 replies
  • Answers 2 answers
  • Subscribers 95 subscribers
  • Views 6495 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Determine the data relation between SAPGroup and SAPTransaction

Carsten Paul
over 11 years ago

Hi All

we do have a larger SAP environment connected to our Q1IM system. The whole environment is synchronized down to the transaction level. We now need to determine which SAP roles contain which transactions and vice versa. I did came up with the following query:

Select t.Ident_SAPTransaction, t.Description, g.GroupName, g.Description

from SAPTransaction t join SAPTransactionHasSAPAuthObject sthsao

on t.UID_SAPTransaction = sthsao.UID_SAPTransaction

join SAPGroupHasAuthObjectElem sghaoe on sghaoe.UID_SAPAuthObject = sthsao.UID_SAPAuthObject

join SAPGroup g on g.UID_SAPGroup = sghaoe.UID_SAPGroup

Does that look correct?

Parents
  • +1 over 11 years ago

    Hi Carsten,

    I don't understand the data structure for this (would need a little bit time - or more ).

    But I have a working solution for you.

    We already have a SQL function named "VI_F_SAP_TransactionForGroup" in the db.

    It needs one parameter: An UID_SAPGroup, this must be a SAP profile.

    If you want to know the assigned SAP transactions to one given SAP profile you can use this one, e.g.:

    select * from dbo.VI_F_SAP_TransactionForGroup('14a76512-241e-4622-936f-10cd958e4683')

    To get a complete list for all SAP profiles I implemented a wrapper for this function.

    (attached)

    You can call it with such a statement:

    select * from dbo.SE_F_SAP_TransactionForGroup()

    It returns the assignment of the SAP transactions to all SAP profiles.

    If you want to know the assignment to the SAP roles (this was the initial demand) you can use this table function for additional joins, e.g.:

    select

        phf.ident_saptransaction,

        gsr.GroupName as 'Single Role'

    from dbo.SE_f_sap_transactionforgroup() phf /*profile has function*/

    join SAPGroupInSAPGroup gig on phf.UID_SAPGroup = gig.UID_SAPGroupChild

    join SAPGroup gsr /*single role*/ on gig.UID_SAPGroupParent = gsr.UID_SAPGroup

    I hope this solves your problem.

    Best regards,

    Steffen

Reply
  • +1 over 11 years ago

    Hi Carsten,

    I don't understand the data structure for this (would need a little bit time - or more ).

    But I have a working solution for you.

    We already have a SQL function named "VI_F_SAP_TransactionForGroup" in the db.

    It needs one parameter: An UID_SAPGroup, this must be a SAP profile.

    If you want to know the assigned SAP transactions to one given SAP profile you can use this one, e.g.:

    select * from dbo.VI_F_SAP_TransactionForGroup('14a76512-241e-4622-936f-10cd958e4683')

    To get a complete list for all SAP profiles I implemented a wrapper for this function.

    (attached)

    You can call it with such a statement:

    select * from dbo.SE_F_SAP_TransactionForGroup()

    It returns the assignment of the SAP transactions to all SAP profiles.

    If you want to know the assignment to the SAP roles (this was the initial demand) you can use this table function for additional joins, e.g.:

    select

        phf.ident_saptransaction,

        gsr.GroupName as 'Single Role'

    from dbo.SE_f_sap_transactionforgroup() phf /*profile has function*/

    join SAPGroupInSAPGroup gig on phf.UID_SAPGroup = gig.UID_SAPGroupChild

    join SAPGroup gsr /*single role*/ on gig.UID_SAPGroupParent = gsr.UID_SAPGroup

    I hope this solves your problem.

    Best regards,

    Steffen

Children
No Data