We had an error with the 3CEngine the other day. Here is the error and the fix in case it helps anyone else or in case I have this same problem later on.
From the Application Engine Output:
File: e:pt84912b-retailpeopletoolssrcpsappengaedebug.hSQL error. Stmt #: 1603 Error Position: 0 Return: 805 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_COMMUNICATION' with unique index 'PS_COMMUNICATION'.
Failed SQL stmt:INSERT INTO PS_COMMUNICATION (COMMON_ID, SEQ_3C, SA_ID_TYPE, COMM_DTTM, INSTITUTION, ADMIN_FUNCTION, COMM_CATEGORY, COMM_CONTEXT, COMM_METHOD, INCLUDE_ENCL, DEPTID, COMM_ID, COMM_DT, COMM_BEGIN_TM, COMM_END_TM, COMPLETED_COMM, COMPLETED_ID, COMPLETED_DT, COMM_DIRECTION, UNSUCCESSFUL, OUTCOME_REASON, SCC_LETTER_CD, LETTER_PRINTED_DT, LETTER_PRINTED_TM, CHECKLIST_SEQ_3C, CHECKLIST_SEQ, COMMENT_PRINT_FLAG, ORG_CONTACT, ORG_DEPARTMENT, ORG_LOCATION, PROCESS_INSTANCE, EXT_ORG_ID, VAR_DATA_SEQ, EMPLID_RELATED, JOINT_COMM, SCC_COMM_LANG, SCC_COMM_MTHD, SCC_COMM_PROC) SELECT A.COMMON_ID, 1, A.SA_ID_TYPE, GETDATE(), A.INSTITUTION, A.ADMIN_FUNCTION, A.COMM_CATEGORY, A.COMM_CONTEXT, A.COMM_METHOD, A.INCLUDE_ENCL, ' ', ' ', { fn CURDATE() }, NULL, NULL, 'N', ' ', NULL, A.COMM_DIRECTION, A.UNSUCCESSFUL, A.OUTCOME_REASON, A.SCC_LETTER_CD, NULL, NULL, A.CHECKLIST_SEQ_3C, A.CHECKLIST_SEQ, A.COMMENT_PRINT_FLAG, 0, 0, 0, 0, ' ', 0, A.EMPLID_RELATED, A.JOINT_COMM, ' ', ' ', ' ' FROM PS_ENG_COMM_TMP4 A WHERE COMMON_ID = '1784656' AND SA_ID_TYPE = 'P' AND INSTITUTION = 'NPCCS' AND EVENT_3CS_ID = 'F24_COMM' AND COMM_KEY = 'F24' AND SEQNO = 331
Process 11123 ABENDED at Step 3CENGINE_LIB.COMMINST.Step02 (SQL) -- RC = 805 (108,524)
The keys on the Communication Table are the COMMON_ID and the SEQ_3C. So, most likely, the problem is that is trying to insert a Sequence number that already exists.
Here is some SQL that you can use to find the problem:
SELECT TOP 200 A.COMMON_ID, A.SEQ_3C, B.SEQ_COMM_LAST
FROM PS_COMMUNICATION A, PS_LAST_3CS_TBL B
WHERE A.SEQ_3C = (SELECT MAX(SEQ_3C)
FROM PS_COMMUNICATION
WHERE COMMON_ID = A.COMMON_ID)
AND A.COMMON_ID = B.COMMON_ID
AND B.SEQ_COMM_LAST < A.SEQ_3C
Here is some SQL that you can use to fix the problm:
UPDATE PS_LAST_3CS_TBL
SET SEQ_COMM_LAST = (SELECT MAX(SEQ_3C)
FROM PS_COMMUNICATION A
WHERE A.COMMON_ID = PS_LAST_3CS_TBL.COMMON_ID)
WHERE EXISTS (SELECT ‘X’
FROM PS_COMMUNICATION A
WHERE A.COMMON_ID = PS_LAST_3CS_TBL.COMMON_ID
AND A.SEQ_3C > PS_LAST_3CS_TBL.SEQ_COMM_LAST
AND A.SEQ_3C = (SELECT MAX(SEQ_3C)
FROM PS_COMMUNICATION A
WHERE A.COMMON_ID = PS_LAST_3CS_TBL.COMMON_ID) );
If you want to look at the code in the App Engine, the Sequence Number is calculated in the 3CENGINE_LIB.COMMLSEQ section. It loads the Sequence number from the PS_LAST_3CS_TBL table.