Clarity

I unlocked a project – as it had been locked by the Project Manager while in Workbench. I do this several times a month and have used the same queries. However, there was something out of place in the current production database and so now I have this issue.


I used the following SQL Queries that I normally use:

select * from SRM_PROJECTS where unique_name = ‘PRJXXXXXXX’

UPDATE SRM_PROJECTS SET LAST_UPDATED_BY = 5000025 WHERE UNIQUE_NAME = ‘PRJXXXXXX’ ;

Commit;

I also used the following SQL queries that were given by CA Support:

select * from SRM_PROJECTS where last_updated_by=-99
update SRM_PROJECTS set last_updated_by=1 where last_updated_by=-99

I ran these as well.

Then to further confirm, I asked my DBA team what query would they have used to accomplish this effort and they provided the following:
UPDATE SRM_PROJECTS SET LAST_UPDATED_BY=5000025 WHERE UNIQUE_NAME='PRJ0XXXXX';
So I ran this one as well.


At this point, I should be able to go back to the Clarity Application and filter for either the Project Name or the Project ID and then unlock the project– but it's not showing up via Portfolio Manager/Projects Module when I filter for the name or project id. However, It does show up, if I put the Object Id in the URL but there is nothing in the Project Properties: Main – General Page. However, it shows information in the other tabs but it seems to be invalid our old. Can anyone provide some insight on the issue and how to get the information back viewable? Also, where do the Workbench Project plans sit?

Please Note: This is currently a 7.5.3 production environment and we are in the mist of upgrading to V12.0.5. I have a staging environment that has a snapshot of the database in v12 in which I compared the data to - - that's how I came to know that the information in v7.5.3 environment is incorrect. The project is viewable in the v12 Staging environment with the correct data.

Hello Anon

I don't really know why do you update the LAST_UPDATED_BY field, however, executing the update taking into account the "-99" users should take this problem out for you.

We had this specific problem on our environment, our solution was to execute, twice a day the following query:

update niku.srm_projects set last_updated_by=1 where last_updated_by=-99

but some times it doesn't work, so we need to stop the bg service and execute:

delete from prlock where prname = 'prInsertServiceLock'

[color=#FD0000]HOWEVER [color].. On every case, it is highly recomended that you post a new Case on CA support. They will know better what to do in this case, and they will know better your environment.

Hope it helps

Rafa

We have had the same issue happen in the 7.x series and even 8.1. The problem happens when the PMs connection to the database is lost during the project save. The last updated by field is not populated properly and the database inserts the -99.

Updating the SRM Projects like you have has always fixed the issue for us in the past and the project attribute data is filled back in. However, I don't believe that the project has remained locked. Have you checked the project to see if it remains locked and if so, removed the lock? (select * from prlock and look for the specific project record ID) Removing the lock, if it remains may be the last piece of the puzzle. If not, a call to support may be warranted.

Hope this helps.

Ruthann

Thank you ALL for the quick response