Tag: sourcecode

Record Type Values

Here are the values for the RECTYPE field in the table that stores the information about the records in PeopleSoft: PSRECDEFN.  Here are what the values mean:

  • 0 = SQL Table
  • 1 = SQL View
  • 2 = Work Record
  • 3 = Subrecord
  • 5 = Dynamic View
  • 6 = Query View
  • 7 = Temp Table

For example, you can do something like this to find all the tables that have the OPRID field:

SELECT A.RECNAME
FROM PSRECDEFN A, PSRECFIELDALL B
WHERE A.RECNAME = B.RECNAME
AND A.RECTYPE = 0
AND B.FIELDNAME = 'OPRID'

See the PeopleTools Tables for more information.

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.

PeopleTools Bug: Job Data CI

This is another look at an old post.  In summary, the problem is that when you create a Component Interface for the Job Data component, PeopleTools errors.  I decided to attempt to diagnose the PeopleTools bug by tracing it.

I tried this in PeopleTools 8.49.

Here are the last lines of the trace file:

1-8280 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=2 length=11 value=SKP_JOB2_CI

1-8281 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=6 length=4 value=4

1-8282 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=6 value=JOB_JR

1-8283 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=2 length=17 value=CONT_SAL_TYPE_BRA

1-8284 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=6 length=4 value=536

1-8285 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-6 type=6 length=4 value=1

1-8286 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-7 type=6 length=4 value=2

1-8287 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-8 type=6 length=4 value=0

1-8288 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-9 type=2 length=17 value=00-00-01-00-02-00

1-8289 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-10 type=2 length=6 value=JOB_JR

1-8290 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-11 type=2 length=0 value=

1-8291 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-12 type=2 length=17 value=CONT_SAL_TYPE_BRA

1-8292 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-13 type=2 length=0 value=

1-8293 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-14 type=6 length=4 value=0

1-8294 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-15 type=6 length=4 value=25008

1-8295 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=2 length=11 value=SKP_JOB2_CI

1-8296 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=6 length=4 value=4

1-8297 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=6 value=JOB_JR

1-8298 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=2 length=11 value=DUTIES_TYPE

1-8299 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=6 length=4 value=523

1-8300 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-6 type=6 length=4 value=1

1-8301 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-7 type=6 length=4 value=2

1-8302 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-8 type=6 length=4 value=0

1-8303 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-9 type=2 length=17 value=00-00-01-00-02-00

1-8304 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-10 type=2 length=6 value=JOB_JR

1-8305 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-11 type=2 length=0 value=

1-8306 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-12 type=2 length=11 value=DUTIES_TYPE

1-8307 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-13 type=2 length=0 value=

1-8308 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-14 type=6 length=4 value=0

1-8309 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-15 type=6 length=4 value=25009

1-8310 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=2 length=11 value=SKP_JOB2_CI

1-8311 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=6 length=4 value=4

1-8312 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=6 value=JOB_JR

1-8313 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=2 length=14 value=INTCP_BUS_UNIT

1-8314 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=6 length=4 value=512

1-8315 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-6 type=6 length=4 value=1

1-8316 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-7 type=6 length=4 value=2

1-8317 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-8 type=6 length=4 value=0

1-8318 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-9 type=2 length=17 value=00-00-01-00-02-00

1-8319 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-10 type=2 length=6 value=JOB_JR

1-8320 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-11 type=2 length=0 value=

1-8321 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-12 type=2 length=14 value=INTCP_BUS_UNIT

1-8322 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-13 type=2 length=0 value=

1-8323 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-14 type=6 length=4 value=0

1-8324 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-15 type=6 length=4 value=25010

1-8325 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=2 length=11 value=SKP_JOB2_CI

1-8326 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=6 length=4 value=4

1-8327 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=6 value=JOB_JR

1-8328 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=2 length=13 value=INTCP_COMPANY

1-8329 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=6 length=4 value=516

1-8330 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-6 type=6 length=4 value=1

1-8331 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-7 type=6 length=4 value=2

1-8332 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-8 type=6 length=4 value=0

1-8333 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-9 type=2 length=17 value=00-00-01-00-02-00

1-8334 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-10 type=2 length=6 value=JOB_JR

1-8335 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-11 type=2 length=0 value=

1-8336 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-12 type=2 length=13 value=INTCP_COMPANY

1-8337 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-13 type=2 length=0 value=

1-8338 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-14 type=6 length=4 value=0

1-8339 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-15 type=6 length=4 value=25011

1-8340 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=2 length=11 value=SKP_JOB2_CI

1-8341 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=6 length=4 value=4

1-8342 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=6 value=JOB_JR

1-8343 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=2 length=14 value=INTCP_COMPANY2

1-8344 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=6 length=4 value=517

1-8345 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-6 type=6 length=4 value=1

1-8346 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-7 type=6 length=4 value=2

1-8347 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-8 type=6 length=4 value=0

1-8348 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-9 type=2 length=17 value=00-00-01-00-02-00

1-8349 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-10 type=2 length=6 value=JOB_JR

1-8350 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-11 type=2 length=0 value=

1-8351 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-12 type=2 length=14 value=INTCP_COMPANY2

1-8352 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-13 type=2 length=0 value=

1-8353 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-14 type=6 length=4 value=0

1-8354 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-15 type=6 length=4 value=25012

1-8355 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=2 length=11 value=SKP_JOB2_CI

1-8356 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=6 length=4 value=4

1-8357 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=6 value=JOB_JR

1-8358 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=2 length=12 value=INTCP_DEPTID

1-8359 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=6 length=4 value=513

1-8360 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-6 type=6 length=4 value=1

1-8361 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-7 type=6 length=4 value=2

1-8362 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-8 type=6 length=4 value=0

1-8363 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-9 type=2 length=17 value=00-00-01-00-02-00

1-8364 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-10 type=2 length=6 value=JOB_JR

1-8365 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-11 type=2 length=0 value=

1-8366 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-12 type=2 length=12 value=INTCP_DEPTID

1-8367 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-13 type=2 length=0 value=

1-8368 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-14 type=6 length=4 value=0

1-8369 06.50.28 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-15 type=6 length=4 value=25013

1-8370 06.50.32 3.335000 CMgrUpdateTransaction::OnAbort

1-8371 06.50.32 0.000000 CMgrUpdateTransaction::OnAbort

1-8372 06.50.32 0.111000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 COM Stmt=SELECT DISPLAYONLY FROM PSOPROBJ A, PSOBJGROUP B WHERE B.ENTTYPE = :1 AND B.ENTNAME = :2 AND A.CLASSID = :3 AND (A.OBJGROUPID = '*ALL DEFINITIONS*' OR A.OBJGROUPID = B.OBJGROUPID) UNION SELECT 2 FROM PSOBJGROUP WHERE ENTTYPE = :4 AND ENTNAME = :5 ORDER BY 1

1-8373 06.50.32 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 SSB column=1 type=6 length=4 scale=0

1-8374 06.50.32 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-1 type=1 length=1 value=D

1-8375 06.50.32 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-2 type=2 length=1 value=

1-8376 06.50.32 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-3 type=2 length=7 value=HCPPALL

1-8377 06.50.32 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-4 type=1 length=1 value=D

1-8378 06.50.32 0.000000 Cur#1.4240.HCM90 RC=0 Dur=0.000000 Bind-5 type=2 length=1 value=

1-8379 06.50.32 0.001000 Cur#1.4240.HCM90 RC=1 Dur=0.000000 Fetch

1-8380 06.50.35 3.214000 Tuxedo session opened { DisconnectAll at01C88868, pid=4240}

Here is the insert statement that the error message reported:

INSERT INTO PSBCITEM ( 
    BCNAME, BCTYPE, BCITEMPARENT, BCITEMNAME, 
    SEQUENCE_NBR_6, BCACCESS, BCSCROLL, BCSCROLLNUM, 
    BCSCROLLNAME, RECNAME, SUBRECNAME, FIELDNAME, COMMENTSHORT, 
    MPDEVICEPERSISTINT, SYNCID
) VALUES ( 
    :1, :2, :3, :4, 
    :5, :6, :7, :8, 
    :9, :10, :11, :12, :13, 
    :14, :15
)

So, I looked up the table, and the keys are the first four fields: BCNAME, BCTYPE, BCITEMPARENT, and BCITEMNAME.

Assuming the last row is the duplicate, that means that another row has the same parent and name as:

Type = 4

Parent = JOB_JR

Item Name = INTCP_DEPTID

I did a search through the trace, and the INTCP_DEPTID only exists in this one location.  So, I don’t know what the problem is — our mystery goes unsolved.  What makes it more confusing is that saving a second time fixes the problem.

Maybe I will pull all of the Bind values into an Excel and see if I can find duplicates that way.  But, that is an exercise for another day.

Querying the Component

Here are some queries to find things out about the component.

This one is a simple list of all the fields in the component (doesn’t take into account subpages, etc):

SELECT CP.PNLGRPNAME, CP.MARKET, CP.DESCR,
           CPG.PNLNAME, CPG.ITEMLABEL,
           PG.DESCR,
           PGF.FIELDTYPE, PGF.LBLTEXT, PGF.RECNAME, PGF.FIELDNAME
FROM PSPNLGRPDEFN CP,
         PSPNLGROUP CPG,
         PSPNLDEFN PG,
         PSPNLFIELD PGF
WHERE CP.PNLGRPNAME = 'JOB_DATA'
     AND CP.MARKET = 'GBL'
    AND CP.PNLGRPNAME = CPG.PNLGRPNAME
    AND CP.MARKET = CP.MARKET
    AND PG.PNLNAME = CPG.PNLNAME
    AND PGF.PNLNAME = PG.PNLNAME;

This one shows just the records involved:

SELECT DISTINCT CP.PNLGRPNAME, CP.MARKET, CP.DESCR,  PGF.RECNAME, R.RECDESCR, R.RECTYPE
FROM PSPNLGRPDEFN CP,
         PSPNLGROUP CPG,
         PSPNLDEFN PG,
         PSPNLFIELD PGF,
         PSRECDEFN R
WHERE CP.PNLGRPNAME = 'JOB_DATA'
     AND CP.MARKET = 'GBL'
    AND CP.PNLGRPNAME = CPG.PNLGRPNAME
    AND CP.MARKET = CP.MARKET
    AND PG.PNLNAME = CPG.PNLNAME
    AND PGF.PNLNAME = PG.PNLNAME
    AND PGF.RECNAME = R.RECNAME;

I hope this helps.

AutoLogin GreaseMonkey Script

Here is a GreaseMonkey Script that changes the languages options on the PeopleSoft login page to users’ names.  When you click on the user’s name, it populates the username and password.  It is great for when you are testing Workflow or something like that.


// ==UserScript==
// @name           PSautologin
// @namespace      http://psst0101.wordpress.com
// @description    Automatic Login for PS
// @include        http://ps_server:*/psp/*/EMPLOYEE/ERP/?cmd=logout
// @include        http://ps_server:*/psp/*/?cmd=login*
// @include        http://ps_server:*/psp/HR90CNV/*/HRMS/?cmd=logout
// ==/UserScript==

// set up jQuery variable
var $;

// Add jQuery
var GM_JQ = document.createElement("script");
GM_JQ.src = "http://code.jquery.com/jquery-latest.min.js";
GM_JQ.type = "text/javascript";

document.body.appendChild(GM_JQ);

// Check if jQuery's loaded
var checker=setInterval(function(){
if(typeof ($ = unsafeWindow.jQuery) != "undefined") {
clearInterval(checker);
letsJQuery();
}
},100);

// All your GM code must be inside this function
function letsJQuery() {
var selectALanguage = $('.pslanguageframe .psloginlabel:contains("Select a Language")');
selectALanguage.text("Select A User:");
$('.pslanguageframe .pslogintext').toggleClass('skpremove');
$('.pslanguageframe .pslogintext').text("Hello");
$('.pslanguageframe .textnormal').remove();
$('.pslanguageframe .pslogintext:first').text("test");
addUser('PS', 'PS');
addUser('VP1', 'VP1');
$('.pslanguageframe .skpremove').remove();

$('table:first table:first > tbody > tr:eq(1) td').html('<a href="http://serverWithList/">Other Instances</a>');
}

function addUser(userName, password) {
var newLink = $('.pslanguageframe .skpremove:first').html("<a href='javascript:login'>" + userName + "</a>").toggleClass('skpremove').children('a');
newLink.attr('userName', userName);
newLink.attr('password', password);
newLink.click(loginwith);
}

function loginwith() {
userName = $(this).attr('userName');
password = $(this).attr('password');

$('#userid').val(userName);
$('#pwd').val(password);

// don't trigger URL for link
return false;
}

COBOL Compile Error

In an attempt to draw more attention to an error, let me copy a comment here.  If you have any ideas on what ps_rookie should do, please comment here.

I suggested taking the dash out of the compile directory, and he said that did not make a difference.

Below is the comment:

Hi DE,

I am trying to Install COBOL for my HCM 9.0 Installation. I have downloaded and installaed the Net Express Cobol Comipler and now I am doing the exact same steps as you have discussed above. I am getting the Object Compiling Error. Pasted below.

ASCII Cobol Compilations Proceeding
Creating Directory C:\TEMP\COBOL-COMBILE
Logging progress to file C:\TEMP\COBOL-COMBILE\CBLBLD.LOG
COBOL compiler found in “C:\Program Files\Micro Focus\Net Express 5.1\Base”
Target directory (C:\PS_HOME\CBLBINA) exists and is writable

Copying source files …

Building the COBOL targets …

File(s) that failed to compile or link:
.
Compile error for AVPACCTL.obj
Compile error for AVPCAE.obj
Compile error for AVPGCLUB.obj
Compile error for CCPMNTPD.obj
Compile error for EGPCSRVC.obj
Compile error for EGPUDTGN.obj
Compile error for FAP00SLD.obj
Compile error for FAPAUTHP.obj
Compile error for FAPBUDMV.obj
Compile error for FAPCLICR.obj
Compile error for FAPCLIOR.obj
Compile error for FAPCLOCX.obj
Compile error for FAPCLOOR.obj
Compile error for FAPCMPR1.obj
Compile error for FAPCNAS9.obj
Compile error for FAPCOR00.obj
Compile error for FAPDISBP.obj
Compile error for FAPDLINB.obj
Compile error for FAPDLOCH.obj
Compile error for FAPDLODB.obj
Compile error for FAPDLOOR.obj
Compile error for FAPEQPAR.obj
Compile error for FAPEQRUN.obj
Compile error for FAPINAS5.obj
Compile error for FAPINAS6.obj
Compile error for FAPINAS7.obj
Compile error for FAPINSS2.obj
Compile error for FAPLBOG1.obj
Compile error for FAPLCAD1.obj
Compile error for FAPLCIA4.obj
Compile error for FAPLCOA4.obj
Compile error for FAPLCOX4.obj
Compile error for FAPLDAD1.obj
Compile error for FAPLDAD2.obj
Compile error for FAPLDOA4.obj
Compile error for FAPLDPN2.obj
Compile error for FAPLORG0.obj
Compile error for FAPPIDA0.obj
Compile error for FAPPKASN.obj
Compile error for FAPPKCTL.obj
Compile error for FAPPKENG.obj
Compile error for FAPPKPST.obj
Compile error for FAPPLBL0.obj
Compile error for FAPPLINB.obj
Compile error for FAPPLODB.obj
Compile error for FAPPLOOR.obj
Compile error for FAPSAR00.obj
Compile error for FAPTALRT.obj
Compile error for FAPTRMU1.obj
Compile error for FAPUPTGT.obj
Compile error for FGPACCRL.obj
Compile error for GPPDPDM5.obj
Compile error for GPPSERVC.obj
Compile error for GPPUERND.obj
Compile error for GPPUSYPN.obj
Compile error for GPPUTAKE.obj
Compile error for PAPCABAL.obj
Compile error for PAPCAERN.obj
Compile error for PAPCOPTF.obj
Compile error for PAPCRCTL.obj
Compile error for PAPCRSQL.obj
Compile error for PAPCSRVC.obj
Compile error for PAPCSSEC.obj
Compile error for PAPPCERN.obj
Compile error for PAPPCHRS.obj
Compile error for PAPPEXPT.obj
Compile error for PAPPPYMT.obj
Compile error for PAPPRSQL.obj
Compile error for PAPUALTL.obj
Compile error for PAPUDTGN.obj
Compile error for PAPUPROJ.obj
Compile error for PIPCMPAR.obj
Compile error for PIPMPORT.obj
Compile error for PIPSPRCS.obj
Compile error for PIPUTLTY.obj
Compile error for PSPBAELG.obj
Compile error for PSPBAELT.obj
Compile error for PSPBAENR.obj
Compile error for PSPBAOPT.obj
Compile error for PSPBASCH.obj
Compile error for PSPBATBL.obj
Compile error for PSPCNTAX.obj
Compile error for PSPCOBRA.obj
Compile error for PSPCPCAL.obj
Compile error for PSPEARRY.obj
Compile error for PSPECALC.obj
Compile error for PSPENANN.obj
Compile error for PSPENNHT.obj
Compile error for PSPFCEXT.obj
Compile error for PSPGCALC.obj
Compile error for PSPGPROR.obj
Compile error for PSPLDTL2.obj
Compile error for PSPMFLSA.obj
Compile error for PSPPARRY.obj
Compile error for PSPPFDED.obj
Compile error for PSPPFDST.obj
Compile error for PSPPFUND.obj
Compile error for PSPPTFND.obj
Compile error for PSPPYNET.obj
Compile error for PSPRPERY.obj
Compile error for PSPTCALC.obj
Compile error for PSPUSTAX.obj
Compile error for PTPSQLGS.obj
Compile error for SFP1098P.obj
Compile error for SFPAPLPY.obj
Compile error for SFPAPPFE.obj
Compile error for SFPAYROG.obj
Compile error for SFPBICOR.obj
Compile error for SFPBILGE.obj
Compile error for SFPCHECS.obj
Compile error for SFPCLCAN.obj
Compile error for SFPCRHST.obj
Compile error for SFPFEADJ.obj
Compile error for SFPFECAL.obj
Compile error for SFPGLINT.obj
Compile error for SFPLATFE.obj
Compile error for SFPLNADJ.obj
Compile error for SFPORGPY.obj
Compile error for SFPORGSH.obj
Compile error for SFPQKORG.obj
Compile error for SFPQKPST.obj
Compile error for SFPQPOST.obj
Compile error for SFPREFND.obj
Compile error for SFPSTDNT.obj
Compile error for SFPTPRCL.obj
Compile error for SRPCAGRP.obj
Compile error for SRPCAPRG.obj
Compile error for SRPCARPE.obj
Compile error for SRPCARQ1.obj
Compile error for SRPCARQL.obj
Compile error for SRPCARQM.obj
Compile error for SRPCAXR2.obj
Compile error for SRPCBART.obj
Compile error for SRPCCARS.obj
Compile error for SRPCCART.obj
Compile error for SRPCCONS.obj
Compile error for SRPCENVB.obj
Compile error for SRPCENVR.obj
Compile error for SRPCEPRD.obj
Compile error for SRPCEPST.obj
Compile error for SRPCERPT.obj
Compile error for SRPCTEXT.obj
The error list is in C:\TEMP\COBOL-COMBILE\ERROR.LIS
.
Aborting this process !!! EXEs/GNTs/INTs are not going to be copied/replaced…
.
Please fix the errors and rerun this batch file !!!!!!!
.

C:\Temp\COBOL-COMBILE>

When I check the Error.lis file, I find the same entries there.

Could you please inform, as to why am I getting this error?

JDBC and SQL Server

Well, I have explored JDBC connections to Access in the past, but here is a connection to SQL Server via ODBC.

A driver for SQL Server exists, but using that driver requires adding a Jar file to the server.  Assuming the App Server or Batch Server is running on Windows, you have to have an ODBC connection setup already.

Here is the code; hope it helps:

Local SQL &sql;
 Local string &loadCommand;

/* Using JDBC */

/* Load the driver */
GetJavaClass("java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
Local string &connectionUrl = "jdbc:odbc:" | %DbName | ";
user="MY_ADMIN";
password="MY_PASSWORD";
Local JavaObject &conn = GetJavaClass("java.sql.DriverManager").getConnection(&connectionUrl);
Local JavaObject &connSource = GetJavaClass("java.sql.DriverManager").getConnection(&connectionUrl);
try
   &conn.setCatalog("MY_DB");
catch Exception &changeCatalogWarning
end-try;
Local JavaObject &stTarget = &conn.createStatement(GetJavaClass("java.sql.ResultSet").TYPE_SCROLL_INSENSITIVE, GetJavaClass("java.sql.ResultSet").CONCUR_UPDATABLE);
Local JavaObject &stSource = &connSource.createStatement(GetJavaClass("java.sql.ResultSet").TYPE_FORWARD_ONLY, GetJavaClass("java.sql.ResultSet").CONCUR_READ_ONLY);
Local JavaObject &rsTarget;
Local JavaObject &rsSource;
Local number &rowsLoaded;
Local number &rowsUntilCommit;
Local JavaObject &metaData;
Local number &columnIndex;

&loadCommand = "";
&loadCommand = &loadCommand | "SELECT top 50 * ";
&loadCommand = &loadCommand | "FROM OPENQUERY([MY_LINKED_SERVER], ";
&loadCommand = &loadCommand | "'SELECT * FROM MY_REMOTE_TABLE')";
&rsSource = &stSource.executeQuery(&loadCommand);
&loadCommand = "";
&loadCommand = &loadCommand | "SELECT * ";
&loadCommand = &loadCommand | "FROM [TARGET_TABLE]";
&rsTarget = &stTarget.executeQuery(&loadCommand);
&rowsLoaded = 0;
&rowsUntilCommit = 50;
&metaData = &rsSource.getMetaData();
While &rsSource.next()
   GetJavaClass("java.lang.Thread").yield();
   &rsTarget.moveToInsertRow();
   For &columnIndex = 1 To &metaData.getColumnCount();
      Evaluate &metaData.getColumnType(&columnIndex)
         When = - 6
            rem &rsTarget.updateInt(&columnIndex, &rsSource.getInt(&columnIndex)); 
            &rsTarget.updateInt(&columnIndex, CreateJavaObject("java.lang.Integer", 0).intValue());
         When = 4
            rem &rsTarget.updateInt(&columnIndex, &rsSource.getInt(&columnIndex));
            &rsTarget.updateInt(&columnIndex, CreateJavaObject("java.lang.Integer", 0).intValue());
         When = 6
            rem &rsTarget.updateFloat(&columnIndex, &rsSource.getFloat(&columnIndex));
            &rsTarget.updateFloat(&columnIndex, CreateJavaObject("java.lang.Float", " ").floatValue());
         When = 12
            rem &rsTarget.updateString(&columnIndex, &rsSource.getString(&columnIndex));
            &rsTarget.updateString(&columnIndex, CreateJavaObject("java.lang.String"));
         When = 93
            rem &rsTarget.updateTimestamp(&columnIndex, &rsSource.getTimestamp(&columnIndex));
            &rsTarget.updateTimestamp(&columnIndex, CreateJavaObject("java.sql.Timestamp", CreateJavaObject("java.util.Date").getTime()));
         When-Other
            rem &rsTarget.updateObject(&columnIndex, &rsSource.getObject(&columnIndex));
            Error ("Unknown column type: " | &metaData.getColumnType(&columnIndex) | " -- " | &metaData.getColumnTypeName(&columnIndex) | " for field " | &metaData.getColumnName(&columnIndex));
      End-Evaluate;
   End-For;
   &rsTarget.insertRow();
   &rowsLoaded = &rowsLoaded + 1;
   &rowsUntilCommit = &rowsUntilCommit - 1;
   If &rowsUntilCommit <= 0 Then;
      &conn.commit();
      CommitWork();
      &rowsUntilCommit = 50;
   End-If;
End-While;
&rsSource.close();
&rsTarget.close();
&conn.commit();
CommitWork();
End-While;

Resources

Looking for Orphan Rows

Here is some SQL that you might find helpful:

SELECT ‘SELECT ”’ + C.RECNAME + ”’ AS RECNAME, OPRID FROM ‘ + D.TABLENAME +
‘ A WHERE NOT EXISTS (SELECT ”X” FROM PSOPRDEFN B WHERE A.OPRID = B.OPRID) UNION ‘
FROM PSRECFIELDALL C,
(SELECT RECNAME, SQLTABLENAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME <> ‘ ‘
AND RECTYPE = 0
UNION
SELECT RECNAME, ‘PS_’ + RECNAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME = ‘ ‘
AND RECTYPE = 0
) D
WHERE C.RECNAME = D.RECNAME
AND C.FIELDNAME = ‘OPRID’

This Query writes a huge SQL Statement that looks for rows in any table that has an OPRID that does not exist in the PSOPRDEFN table.

Here is how to use it:

  • Run the SQL Statement
  • Copy all of the rows as a new query
  • Remove the last Union from the last line
  • You might want to add “ORDER BY 1” at the end to get it in record order
  • Execute the newly generated query

Note, this pattern can be replicated for other fields or situations too.  It is not just limited to use with OPRID.

Step By Step Virtual PS Install: Database Creation

This is a continuation of my virtual PeopleSoft installation.  In this step, we will create the database in an already installed Oracle Database system.  So, this assumes that the Oracle Database software is already installed.  Loading the PeopleTools data will be covered in the next step.  To see the complete list of steps, click here.

Read More