Clarity PPM

Expand all | Collapse all

Reading user ID of current session in a dynamic query

Jump to Best Answer
  • 1.  Reading user ID of current session in a dynamic query

    Posted 11-30-2017 05:19 PM

    Google has not been my friend lately. I've done this before and I'm sure others have a thousand times but can't find the answer.

     

    I want to create a dynamic query based portlet and read the user id of the current session then based on that present some information about the projects they are involved in (not team but certain resource lookup based fields).

     

    I am looking for the syntax to use in the sql portlet to read the current session user id. Can someone share this? Thanks.



  • 2.  Re: Reading user ID of current session in a dynamic query

    Posted 11-30-2017 09:35 PM

    Hi,

     

    There is CMN_SESSIONS table and it has the following columns.

    If someone login PPM, then record will be inserted into this table.

    If user logout PPM, then record will be removed.

     

    ------------------------------------------------------------

    ID                                          NUMBER
    USER_ID                              NUMBER
    CREATED_DATE                 DATE
    CREATED_BY                      NUMBER
    LAST_UPDATED_DATE      DATE
    LAST_UPDATED_BY          NUMBER
    TOKEN                                 VARCHAR2(255)

    --------------------------------------------------------------

     

    Following NSQL will provide current login user name and session token.

     

    ---------------------------------------------------------------

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:U.ID:ID@,
                     @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.USER_NAME:USER_NAME@,
                     @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:S.TOKEN:SESSION_TOKEN@
    FROM       CMN_SEC_USERS U,
                     CMN_SESSIONS S
    WHERE    S.USER_ID = U.ID
    AND          @FILTER@

    -----------------------------------------------------------------

     

    I hope this is helpful for your investigation.

     

    Thank you.



  • 3.  Re: Reading user ID of current session in a dynamic query
    Best Answer

    Posted 12-01-2017 12:28 AM

    Hi SriniVenkat 

     

    If you are looking for NSQL Portlet or lookup which brings data as per User logged in, then you can use CA PPM built in parameter @WHERE:PARAM:USER_ID@ in the system for achieving that.

     

    For example if you want to bring data for all project where current logged in user is project manager is as below:

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:U.ID:ID@,
                   @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:U.code:inv_code@,
                   @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:U.name:inv_name@
    FROM INV_INVESTMENTS U
    WHERE 

    U.MANAGER_ID = @WHERE:PARAM:USER_ID@
    AND @FILTER@

     

    Regards,

    Prashank Singh



  • 4.  Re: Reading user ID of current session in a dynamic query

    Posted 12-01-2017 12:40 PM

    Thanks so much for your replies. It is simpler than I thought. Prashank - I'll be using the approach you've mentioned.