Tag: PeopleSoft

PeopleSoft Campus Solutions

ORA-28000: the account is locked – PeopleSoft

Hey everyone,

Just a small PeopleSoft issue I ran into while running an SQR:

(SQR 5528) ORACLE OCISessionBegin(RDBMS) error 28000 in cursor 0:
   ORA-28000: the account is locked
(SQR 4701) Cannot logon to the database.

SQR for PeopleSoft: Program Aborting.

For some reason the sysadm account had been locked, thankfully there’s a fairly easy fix:

ALTER USER sysadm ACCOUNT UNLOCK;

If you don’t have access you may need to get a DBA to run it.

Reserved Words – Oracle

Just a quick post on how to check if a word is reserved using the v$reserved_words:

SELECT *
FROM v$reserved_words
WHERE reserved = 'Y'

Alernatively you can specify the word, i.e. to check if online is a reserved word, run the following:

SELECT *
FROM v$reserved_words
WHERE UPPER(keyword) LIKE '%ONLINE%'

Access Log – PeopleSoft

I came across a requirement today where I needed to find out when a user had last logged into PeopleSoft. The record PSACCESSLOG came in handy here, it stores all of the following:

PSACCESSLOG
OPRID: The users operator ID
LOGIPADDRESS: The users IP address
LOGINDTTM: A timestamp showing when the user logged in
LOGOUTDTTM: A timestamp showing when the user logged out
PT_SIGNON_TYPE: The signon type

Find Last Login Date for User:

SELECT *
FROM PSACCESSLOG
WHERE oprid = ''
ORDER BY effdt DESC;

Find all logins since 1st March 2012:

SELECT *
FROM PSACCESSLOG
WHERE TO_DATE(CAST(logindttm AS DATE), 'dd/mm/yy') >= TO_DATE('01/03/2012', 'dd/mm/yy');
PSACCESSLOG - Login History in PeopleSoft
PSACCESSLOG - Login History in PeopleSoft

Leave a comment below if you have any questions.

How to Edit a Message Catalog Definition – PeopleSoft

Editing a message catalog definition in PeopleSoft is pretty straight forward, simply browse to the following menu path:

PeopleTools > Utilities > Administration > Message Catalog

Enter your message set number into the prompt. Note that if you do not know your message set number you can use the following to find it:

SELECT *
FROM PSMSGCATDEFN
WHERE MESSAGE_SET_NBR = '12345'
      AND MESSAGE_NBR = '12345';
      
SELECT *
FROM PSMSGCATDEFN
WHERE MESSAGE_TEXT LIKE '%what your message is%';

Once you have your message simply edit the text and description appropriately then save!

Sleep Function Within PeopleCode – PeopleSoft

While at work today I discovered that there doesn’t appear to be any built in functionality to allow for a delay to be implemented within PeopleCode. Thankfully there are a couple of roundabout way you can go about doing this:

#1: Works if you are sitting on an Oracle DB

/* The following code creates a 5 second delay using DBMS_LOCK.SLEEP */
&s = 5;
SQLExec("exec DBMS_LOCK.SLEEP(:1)", &s);

#2: Note that this method is not as efficient as DMBS_LOCK.SLEEP()

/* The following code creates a 5 second delay using the java sleep method */
&s = 5;
GetJavaClass("java.lang.Thread").sleep(1000 * &s);