Y2K10 PeopleSoft Style

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2011/01/10/tip-comparing-trace-files/

I was surprised to find today that many of the PeopleTools items on the menu were missing.  Then, someone pointed me to article 1183084.1 on Oracle Support.

The problem is that many of the content references have a Expiration Date or Valid To date of 12/31/2010.  If you have some patience, you can go though Portal Structure and Content and update each one.  But, PeopleSoft has an Application Engine program attached to the article that will automatically do it.

Running the program is pretty straight forward.  The only thing I had to do was adjust the last updated field.  The program only updates content references that were last updated by PPLSOFT, but most of my content references were marked with PS.  You could either update the PSPRSMDEFN table manually in the database, but I changed all of the references for ” = ‘PPLSOFT'” to ” IN (‘PPLSOFT’, ‘PS’)”.

Advertisements

UPK Publish Preview Problem

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2010/06/15/upk-publish-preview-problem/

I have been learning the Oracle UPK product recently, and I was having trouble with the publish preview.  I was getting this error message: “preview requires intranet settings to be enabled. These settings are located on the security tab in the Internet Settings of Internet Explorer.”.

I found a post that gave some options for fixing this.  Unfortunately, I did not see the “Automatically detect intranet network” option that they were talking about.  I was running Internet Explorer 7, and I think my problem was that my OS was Windows Server 2003.  I think the Enhanced Internet Explorer Security was enabled, but I looked to try to see if I could uninstall it.  I also tried changing my default browser to Firefox, but it still opens IE to launch the preview.

The solution that ended up working was simply upgrading to Internet Explorer 8.  I am curious if anyone else has had the same problem and how they fixed it.

Letter Generation Error

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2010/01/18/letter-generation-error/

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.