We have installed PMO Accelerator on Clarity 12.0.5, and are using the PMO Accelerator Status Report Object (stored in table ODF_CA_COP_PRJ_STATUSRPT). We have a process that runs several times a day that locks all of the visible status report attributes on any status report instance that is either over a week old or is not the latest instance for its Project parent. This process uses a GEL script to execute the following SQL statement:
INSERT INTO ODF_LOCKED_ATTRIBUTES
(ID, OBJECT_CODE, ODF_PK, ATTRIBUTE_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY)
SELECT (SELECT MAX(ID) FROM CLARADM.ODF_LOCKED_ATTRIBUTES) + ROWNUM AS ID, Q.*
FROM (
SELECT DISTINCT
'cop_prj_statusrpt' AS OBJECT_CODE,
X.ID
AS ODF_PK,
C.INTERNAL_NAME AS ATTRIBUTE_CODE,
SYSDATE
AS CREATED_DATE,
1
AS CREATED_BY,
SYSDATE
AS LAST_UPDATED_DATE,
1
AS LAST_UPDATED_BY
FROM CLARADM.ODF_VIEW_ATTRIBUTES A
JOIN CLARADM.ODF_CUSTOM_ATTRIBUTES C ON C.INTERNAL_NAME = A.ATTRIBUTE_CODE
JOIN CLARADM.ODF_VIEWS V ON V.ID = A.VIEW_ID
AND V.OBJECT_CODE = C.OBJECT_NAME
JOIN (SELECT ID
FROM CLARADM.ODF_CA_COP_PRJ_STATUSRPT
WHERE (CREATED_DATE BETWEEN TO_DATE('01/01/2001', 'mm/dd/yyyy') AND SYSDATE - 7
OR ID NOT IN
(SELECT MAX(ID) FROM CLARADM.ODF_CA_COP_PRJ_STATUSRPT GROUP BY ODF_PARENT_ID))
AND ID NOT IN
(SELECT ODF_PK FROM CLARADM.ODF_LOCKED_ATTRIBUTES WHERE OBJECT_CODE = 'cop_prj_statusrpt'))
X ON 1=1
WHERE C.OBJECT_NAME = 'cop_prj_statusrpt'
AND C.IS_ACTIVE = 1
AND V.VIEW_TYPE = 'property'
AND A.LABEL
!= 'in captions nls'
AND A.LABEL NOT LIKE 'Project %'
AND A.ATTRIBUTE_CODE NOT IN ('code', 'name')) Q
This process (that is, the SQL INSERT statement above) is working as designed; that is, it is selecting the desired status report instances to lock and it is successfully locking them. However, another process we have that uses the System Action “Lock Attributes” is regularly failing with the error:
BPM-0522 Internal Process Engine Error.
My guess is that, since we are adding rows to the table ODF_LOCKED_ATTRIBUTES as it were “outside” the normal regime of Clarity’s application code, when Clarity itself goes to add rows to this same table it is using an ID field value that we have already used. Since these ID field values must be unique, Clarity’s INSERT fails. If I knew where Clarity was going to get its “next ID” value, I could update that location after doing the INSERTs above … but I can’t find that place! Thought it might be the table PRCOUNTER, but the contents of that table make clear that it is not.
Anybody have any suggestions for where to look next?