Clarity

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.

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.

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

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