We just upgraded our instance of PPM 8.0 to SP3. And the users immediately started reporting problems with some fields in Project Risk request types. In particular the Assigned To: field where Transaction History is enabled.
When the field is changed - it takes 10 - 15 minutes to save the request.
If we disable the Transaction History - it saves with no delays. Also, we found that the cuase of the problem is the trigger KCRT_REQ_RHT_AUDIT_[header type id]_1. If we disable this trigger when Transaction History is enabled on the field the request saves with no delay too.
This behavior has not been observed before SP3.
there is currently an incident with RnD under investigation for an identical issue on 9.10.
QCIM1L42302 (internal only):
Whenver we change the seeded 'Assigned To;' field for Project controlled request types like Risk and Issue request Types,It is taking significant delay to change the value. Please note that notes History for this seeded fields is on. When we disable the notes history the delay issue vanishes. There has been similar issue in the previous version and i belive there has been one-off patch for this fix. Could You Please provide the pacth for this issue.
I suggest you open a new case and submit your current findings.
There WAS a suggested workaround but for the incident at hand, it doesn#t seem to work. If you have will and time, you could test it and let us know if it helps:
The fix is to use a custom validation to reduce to one "table" by putting everything into a view such that the autocomplete sql is simple like:
select id, name from my_wonderful_v where...
The "CRT - Assigned To - Enabled" validation is an example of the problem. Use the seeded "CRT - Assigned To - Enabled" validation in a test against a large database and this causes poor performance due to no joins (see "CRT - Assigned To - Enabled.zip" for full trigger info):
SELECT NVL(MAX( FULL_NAME),TO_CHAR(:NEW.ASSIGNED_TO_USER_ID))
INTO L_TMP_NEW FROM KNTA_USERS USERS, KNTA_LOOKUPS LD WHERE USER_ID = :NEW.ASSIGNED_TO_USER_ID;).
"CRT - Assigned To - Enabled" validation audit trail needs to be optimized. The audit trail code should optimize queries based on the custom validations (or give warnings that when using a custom sql it will be used in the audit trail).
I found that the incident has been closed, but no defect or anything created for it.
The issue was with the trigger of the Request Header Type.
Please check this trigger: KCRT_REQ_RHT_AUDIT_<request header type id>_1 or KCRT_REQ_RHT_AUDIT_<request header type id>_2.
(NOTE: You need to replace the request header type id with real ID).
You should find the following SQL scripts inside the trigger:
IF (:new.ASSIGNED_TO_USER_ID IS NOT NULL) THEN
SELECT NVL(MAX( USERS.FULL_NAME),to_char(:new.ASSIGNED_TO_USER_ID))
INTO l_tmp_new FROM KNTA_USERS USERS, KNTA_USERS MANAGERS, KNTA_LOOKUPS LD WHERE USERS.USER_ID = :new.ASSIGNED_TO_USER_ID;
l_tmp_new := ' ';
IF (:old.ASSIGNED_TO_USER_ID IS NOT NULL) THEN
SELECT NVL(MAX( USERS.FULL_NAME),to_char(:old.ASSIGNED_TO_USER_ID))
INTO l_tmp_old FROM KNTA_USERS USERS, KNTA_USERS MANAGERS, KNTA_LOOKUPS LD WHERE USERS.USER_ID = :old.ASSIGNED_TO_USER_ID;
l_tmp_old := ' ';
We can find three tables (KNTA_USERS USERS, KNTA_USERS MANAGERS, KNTA_LOOKUPS LD) which are not joined.
One possible solution (given for this ticket) was to remove 2 useless tables: KNTA_USERS MANAGERS, KNTA_LOOKUPS LD from the SQL.
These SQL scripts are part of the trigger which will be invoked when users change the value of "assign to".
Now, this will not be trivial. You should be aware of testing this first on any dev or test-environment that you have to be sure it will work and impact your environment positively before going live with it. Also make sure to recompile the trigger after you changed the scripts within it. Lastly, make sure that you disable the previous triggers if a recompile should create new ones.
All in all, there are a couple of possible issues, this is one of them. If you want to test the solution do that, in case of any doubts please don’t play around, especially not on your prod environment – rather than that, please then log a ticket with HP Support, explain the problem, deliver logs and triggers and make sure you give explicit information about how the problem shows, the incident that was mentioned in the forum etc. The more information you were to give, the better the chances of a quick and clean solution.
Hope it helps. Wish you the best in any case.