Tuesday, December 19, 2006

Data being added conflicts with existing data

If users are encountering this error, chances are that your sequence no. table is out of synch with your main transaction table. For e.g. this can happen while hiring a new employee and a new employee id needs to be generated. PeopleSoft delivered code uses SQLExec approach to

  • First Update the Sequence to next number
  • Selecting the sequence from the table
  • Assign this to page field

This method works fine, except where you are calling this function conditionally in Saveprechange event and Subsequent workflow or savepostchange event errors out and PeopleSoft issues rollback and all changes to database are rolled back. This results in Sequence no table not being updated to next value. However the actual page field is updated with this new value. If the user is able to correct the error and save the page again, the peoplecode to update the sequence does not fire due to it's conditional execution. (for e.g. EMPLID = 'NEW' as emplid is already equal to a new value). Remember, error does not clear the page values only rolls back database changes. This results in your emplid in person table higher than last employee id in installation table. Now when the users trying to hire new employees it gives the above error.

The best way to avoid this is to use GetNextNumberWithGaps peoplecode function, which automatically increments the counter and commits the value to database. This also improves the database locking.

If for some reason you can not use this function, you can still use GetNextNumber or SQLExec and make sure that this gets executed all the time, even after the error has occurred in Workflow or savepostchange event. Instead of evaluating the current value of emplid = 'NEW' , store the value when the user enters the component in a component variable and use that variable for comparison. This will ensure that your update logic is firing all the time.

Here is the Old PeopleCode

Function assign_employee_id(&EMPLID);
&CHECK = Rept("9", &LENGTH);
If Value(&EMPLID) > Value(&CHECK) Then
Error MsgGet(1000, 74, "The maximum Employee ID of %1 has been assigned.", &CHECK);
&EMPLID = Rept("0", &LENGTH - Len(&EMPLID)) | &EMPLID;

Here is the new PeopleCode

Function assign_employee_id(&EMPLID);
&CHECK = Rept("9", &LENGTH);
Evaluate &EMPLID
When = %GetNextNumber_SQLFailure
/* Do Error processing */
When = %GetNextNumber_TooBig
Error MsgGet(1000, 74, "The maximum Employee ID of %1 has been assigned.", &CHECK);
When = %GetNextNumber_NotFound
/* Do Error processing */
&EMPLID = Rept("0", &LENGTH - Len(&EMPLID)) | &EMPLID;

Friday, December 15, 2006

Search PeopleSoft Customer Connection for Solutions to most common issues

PeopleSoft customer connection website has been recently enhanced with a new feature : Knowledge browser. This will allow you to look for most common resolutions easily. You can also download a spreadsheet containing solutions by product area.

Navigation : Support -> Online Support -> Knowledge Browser -> Peoplesoft Enterprise

Direct URL : http://urlic.com/knowledgebrowser (Enter your customer connection Userid and password). Following High level topics are available.

Monday, December 11, 2006

Download Oracle OpenWorld Presentations for Peoplesoft

You can download Oracle OpenWorld Presentations at http://urlic.com/openworld2006

Enter the following in the popup dialog box.
Username: cboracle
Password: oraclec6

There are lot of useful presentations on PeopleSoft Upgrade. To search for peopleSoft technology related presentations, use following
Search In: Sessions
Key Topics: All
Track: Application Technology
Special Interests: PeopleSoft Enterprise

or go to this URL at PeopleSoft Technology Presentations - Oracle Open World 2006

This returns total of 59 records.

Thursday, October 19, 2006

Search Customer Connection Effectively

Type following Keyword : SPOTLIGHT and any other keyword to find most important resolutions. For e.g. To search for Crystal Relate issues, search for

These resolutions are specially created to address specific issues. I just learned it today in Advisor webcast : PeopleTools 8.48 : Integration broker overview.

Friday, October 06, 2006

Impact of U.S. Daylight Saving Time Changes in 2007 on peoplesoft

US DST changes may require updating the JRE used in Web/App/Batch servers. More information can be found in these links



Do you think it will also impact peopletools release as well. I do not see any documents on this on customer connection. Please share your thoughts on this issue.

I think this PeopleSoft Page and associated data stored in PSTIMEZONE and PSDSTTIME must be updated. I have seen one other record PSTZOFFSET which may need to be updated, if you are using this in your environment. The data in this table is not required by peoplesoft and can be used in query to simplify the access to timezone data. This makes the time zone information available in a format that can be easily accessed with SQL. (Exception : May be used by Time and Labor. Check your installation). You may also want to update only for the period 2007 and onwards after applying the updated timezone information.

PeopleTools -> Utilities -> International -> TimeZones and Hit the button Generate Query Offsets and enter the start and end date for which you want to generate the information. This will delete the old data and enter the new data. You may want to update the data only for the year 2007 onwards only.

Here is a link to peoplebooks -> Unserstanding Time Zones

Navigation for PeopleTools 8.4x

PeopleTools -> Utilities -> International -> Time zones
Menu: Utilities

Here is the SQL to update the record.

INSERT INTO PSDSTTIME VALUES ( '2FirstSunNov', 'N', '11', 1, '0', 2, 0, 'First Sunday in Nov, 2:00am' )
INSERT INTO PSDSTTIME VALUES ( '22ndSunMar', 'N', '3', 2, '0', 2, 0, 'Second Sunday in March, 2:00am' )

Tuesday, October 03, 2006

Define Optional Query Prompt/Criteria in PeopleSoft Query

To create a optional query criteria, in Peoplesoft Query create a prompt as expression as shown below
:1 OR :1 = ' '
Note their is a single space between two quotes. This will make query run with or without prompt. User can either provide the prompt value to return results for a specific value or leave it blank to return rows for all values.

Tuesday, September 26, 2006

Execute query from Lefthand Portal Navigation

You can run PeopleSoft Query from Lefthand Portal Navigation. This helps the end user to navigate to remember the query name easily.

Go to Portal -> Structure and Content

Navigate to  the folder under which you want to create the content reference.

Add a new content reference and fill following fields as follows

Check No Template check box. This will remove the portal Header and Left hand menu navigation.

URL information

Node Name : Always use Local or appropriate remote node name

URL Type : PeopleSoft Generic URL


Content Reference Attributes


Uncheck Translate check box.

Attribute value: true

This will open the query results in a new window.

Go to Security Tab and add appropriate permission list and save the content reference.

You may have to refresh the homepage to be able to see this new portal content reference in Left hand  Navigation menu.

This is applicable for PeopleTools 8.4x only.

Wednesday, September 20, 2006

Target Operator ID has 'No Access' to Upgrade. (62,14)

when you try to copy a project from one database to anonther, you get a message Target Operator ID has 'No Access' to Upgrade. (62,14). This is despite you have correct permission list assigned to the userid in target database. This is due to bug in PeopleTools and is fixed in 8.46. Here is a workaround. Run this query in your target database.


The operator class or permission list returned by very first row of query results, must have access to upgrade for that user. (AUTHORIZEDACTIONS = 4 for MENUNAME = APPLICATION_DESIGNER and BARNAME = UPGRADE).

Open Permission List, Go to PeopleTools Tab. Select Tools Permissions Link and Asign Full Access for Upgrade (at the bottom). Generally permission list PTPT1200 and Role PeopleTools has access to copy the project to target database.

Friday, August 18, 2006

Reset your PeopleSoft Password or AccessID Password

Many times you may get a request from user to reset his password and you can not login to PeopleSoft Web App to do that. Here is a way to reset the password using your database SQL utility. This assumes that you can login to your database using valid Peoplesoft db account or superuser account.


This resets the password = oprid

update psoprdefn
set operpswd = oprid,
encrypted = 0
where oprid = any_oprid;

Login to data mover (bootstrap mode or regular mode)
and run encrypt_password oprid;

2. For some reason, you login to Appdesigner and it gives following error message

SQL error. Stmt #: 2 Error Position: 0 Return: 404 - ORA-01017: invalid username/password; logon denied;

This indicated that your database password and password stroed in PSACCESSPRFL (Encrypted format in accesspswd) are not in sync.
Run the following command in your database SQL Utility

set accessid = 'peoplesoft_schema_id',
accesspswd = 'current_password',
encrypted = 0
where symbolicid = <your symbolic id>

Login to App designer and it will let you login again and also encrypt the password in PSACCESSPRFL

Tuesday, August 08, 2006

Another Application Designer is the Debugger. (143,7) error

If you are getting this error Another Application Designer is the Debugger. (143,7) error then try the following

Restart the application server after clearing the cache.

Thursday, July 13, 2006

Crystal Report - Sub Report Unhandled Exception

If you are using sub reports in crystal Report and you want to suppress them, You must insert each sub report in a new section and suppress the section using the formula instead of suppressing the sub report directly. Otherwise it results in Unhandled exception error. This happens if the there are no rows returned by sub report for the last row of the main query. Assuming these sub reports are linked.

Wednesday, June 21, 2006

Portal registry search index No Success

Whenever I run the portal registry search index process (8.43.17) for all languages installed (CFR - French, ENG - English, ESP - Spanish), it errors out with No success message. The dump file shows the following error. The app engine process eventually runs fine, after multiple restart. Has anybody encountered similar issues?

PSAESRV.28984 [06/21/06 10:25:55 CkSta1008857](0)

PSAESRV.28984 [06/21/06 10:25:55 CkSta1008857](0) PSAESRV crash dump at 2006-06-21 [06/21/06 10:25:55 CkSta1008857](0) PSAESRV.28984 [06/21/06 10:25:55 CkSta1008857](0) Assertion (!nSqlRecurse) Failed at /local_home/build84/pt84317-retailr_o/src/pssys/samlib.cpp, line 1404.PSAESRV.28984 [06/21/06 10:25:55 CkSta1008857](0) Assertion (!nSqlRecurse) Failed at /local_home/build84/pt84317-retailr_o/src/pssys/samlib.cpp, line 1404.

Tuesday, June 20, 2006

Create Portal Registry Content Reference for a Query

You can easily create a new Content Reference for running the Query directly from Left hand menu, without navigating thru Query viewer. This allows easy execution of query by end user.

The Query URL is of the following format


Go to Structure and Content and Create a new Content reference, with following properties

Usage Type : Target
Select No Template check box.
Node Type : Always use Local, if you want to run the query hosted locally
URL Type : PeopleSoft Generic URL
Note : No forward slash before q. Otherwise Portal template will not be removed.
Content Reference Attributes
Attribute Value : true
Uncheck Translate check box.

This will open the query page in a new window with no Portal wrapper.

If you want to pass parameters to query URL, you need to add the following at the end
"&<unique prompt name>=<value>"

You can find the unique prompt name from prompt definition page.

Wednesday, May 31, 2006

NewLine in Workflow Emails

If you want to insert a new line in workflow emails, add a derived/work record field to the page (newline). Set the value of this field as follows

derived_hr.newline = Char(13) | Char(10);

Add this field to Email mapping

Tuesday, May 30, 2006

Downloadble PeopleSoft Software and Documentation

Click here to download the PeopleSoft Enterprise Software

PeopleSoft Software

You can find the licensecodes at the following site

License Codes

You can download the PDF version of PeopleSoft Enterprise documentation here.

PeopleSoft Enterprise Documentation

Archive PeopleSoft Enterprise Documentation

Thursday, May 04, 2006

Directory Interface Troubleshooting Tips

If Directory interface fails to update the Directory Server, an easy way to find list of upload errors is
select * from ps_eo_dsbiload_err order by ds_time_Stamp desc
Common Errors
Bad parameter to an ldap routine

Resolution: This error occurs, if you do not have cert7.db file present in your appserver domain directory. $PS_HOME/appserv/domainname/cert7.db. Make sure that this file is present in all the appservers not just one, configured for your database. I have seen this error appearing randomly, when the cert7.db file was present in only one domain and absent in other domain. Remember to copy this file, if you use SSL for LDAP, everytime you add a new appserver for your database.

If you use the custom function to modify the attribute values, and inside this custom function you change the value of
&AttrIN (Holds the value of the Rec.Field assigned to the current
Attribute.) For e.g. any assignment statement like

&AttrIN = GetCommonName(&AtttIN) or xyz;

will modify the value of &AttrIN and cause the above error. Never modify
this input variable.

No such attribute

This happens, when PeopleSoft Business interlink is trying to delete the
Attribute in LDAP and the value is not present. This is normally harmless.

I have also noticed that Function modifiers are not applied when it generated

Can't connect to the LDAP server
  1. Invalid LDAP Directory Server Address
  2. Invalid Directory Server Port
  3. If using SSL, Expired SSL Certificate or invalid SSL Port
  4. No cert7.db locaed inside the appserver domain for e.g. $PS_HOME/appserv/domainname/cert7.db
  5. The above directory name is valid only if
    If you have not modified the SSL_DB parameter under the settings tab in Business interlink LDAP_SEARCH or LDAP_SEARCH_BIND. For more information, check LDAP Authentication with PeopleTools Red Paper by Tom Lenz on customer connection (Last updated June 24 2005).

Please share any other errors and possible solution you encountered in setting up LDAP Directory interface process.

Tuesday, April 04, 2006

Run PeopleSoft Query from Excel or to Excel

I got inspired from this post in PeopleSoft Corner.

I found following issues with the above solution.
  • You need to use third party XML PeopleCode Library

  • XML files generated by this process can be viewed only in Microsoft Excel 2003

  • XML Link is buggy in PeopleTools 8.43. It does not support special characters (#,$) etc. in password field. So if your site has implemented password controls which requires special characters, XML Link servelet will fail in 8.43 with error message Error: The return content is not xmllink data, please check log file.

  • Also XML Link does not use configuration.properties parameters defaultXMLLinkUSERID and defaultXMLLinkPWD. It defaults to userid and pwd supplied in the url

You can use following URL in IE or open it from inside Excel File Open dialog box. Remove the breaks from the URL before pasting it in ie or excel. Also change the portal registry (EMPLOYEE) and Portal Node (EMPL) and Site ID (ps) for your site.


You will need to create this IScript Function inside a Web Library Record (WEBLIB_XXX) as follows. You also need to assign the ISCript to appropriate permission list

Download Source Code

Friday, February 24, 2006

Developer's Short cut to launch AE programs as processes

You can use this method to launch AE programs as processes without having specific individual Process Definitions created using AE Request Page.

The idea is to replace %%PRCSNAME%% with %%RUNCNTLID%% so that you run AE Programs by creating run control id = AE Program name.

1. Create a New Process Definition FASTTEST of Type App engine and in the OverRide Options Tab, Specify Parameter List -> Override as follows


Also associate it with AE_REQUEST component & TLSALL Process Group on the Process Defintion options tab

You can then navigate to PeopleTools -> Application Engine -> Request AE -> Add
Program name AEPROGNAME

When the Request page opens, select process frequency always and hit the run button. Select the server Name and FASTTEST.

Note: You will not get Redirected Terminal Output (the standard output for the Application Engine program 2-tier). If run via Process Scheduler, there will be no log file generated, because a specific Process Definition for the Application Engine program was not used. Also use this method to test only in Dev Environment.

Thursday, February 23, 2006

Initialize Application Engine State Records

Sometimes you need to Re initialize application angine state record (AET) to its default values in your steps other than the first one. Instead of using the SetDefault peoplecode for each field of the state record, you can do this using a simple peoplecode function in peoplecode action or using %SelectInit Meta SQL construct in SQL action.

PeopleCode Solution.
Function SetDefaultValues(&rec As Record)
For &I = 1 To &rec.FieldCount
&statefld = &rec.GetField(&I);
/* PROCESS_INSTANCE is a reserved state field that cannot be modified */
If &statefld.Name <> "PROCESS_INSTANCE" Then

/* Sample PeopleCode to Call this function */
Declare Function SetDefaultValues PeopleCode FUNCLIB_UTIL.FUNCTION_FIELD FieldFormula;
Local Record &staterec;
Local Field &statefld;

/* Get the Record variable for the State Record) */
&staterec = GetRecord(Record.GF_ATTACH_AET);
MY_AET.OPRID = "xxxxxx";
MessageBox(0, "test", 0, 0, "Oprid before default = " | MY_AET.OPRID);
MessageBox(0, "test", 0, 0, "Oprid after default = " | MY_AET.OPRID);

Note: MessageBox function in application engine peoplecode generates the entry in Message Log and can be viewd in process monitor using Log/Details link.

SQL Step Solution

You can add the SQL Step and use %SelectInit to initialize the State Record.

you can include a SQL Step with following values
%Select Init(State Record fields1, State Record fields2)
select 'x', 'x', 'x' ..... from ps_installation where 1 = 2

This meta-SQL construct is identical to %SELECT with the following exception?if the SELECT returns no rows, %SelectInit reinitializes the buffers. In the case of a %SELECT and no rows are returned, the state record fields retain their previous values.

Monitor App Messaging Errors

PeopleSoft provide an application engine program to monitor the error status in application messages. However it involves more setup to schedule this program. It is important to monitor these messages, as No further messages will be processed, if one of them errors out. Here are 3 simple SQL scripts that can be used to Monitor the error Status for messages

Various Status Codes and their descriptions
0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled
Message Instance Script
Publication Contract Script
Subscription Contract Script

Monday, February 20, 2006

Batch file to Check status of multiple PeopleSoft Process Scheduler Servers running on Windows 2000 Server

You can use this batch file to check status of multiple peoplesoft process scheduler servers (Batch server), running on Windows 2000 Server. You need to run this batch file from %PS_HOME%\appserv directory. Preferably create a shortcut on desktop pointed to this file with Start In folder setup to %PS_HOME%\appserv folder. You will also need to create a View prcsserver.txt or Download prcsserver.txt file, which should contain name of process scheduler servers one per line. Place this file in the same folder, where your batch file is located.
Once you run this batch file, it opens a notepad displaying the status.txt file. It also creates a status1.txt file that contains tmadmin Version amd Copyright information. These files are all created under the same folder where batch file is located.

You can also edit the batch file variables to give different file name. They are
  • outfile - Output File that displays the status
  • outfile1 - Output File that contains tmadmin Copyright info
  • filename - List of Process Scheduler Servers

Tuesday, February 14, 2006

Friday, February 10, 2006

Trace Unique PeopleSoft Session

If you want to generate trace for your own PeopleSoft Session (8.x) and not for everybody else, you can append trace=y to the login URL. For e.g. if the login URL is
http://yourwebserver.com/psp/ps/?cmd=login adding &trace=y at the end will show you special trace settings you can enable. The url will look like

Friday, January 20, 2006

Monitor PeopleSoft Application Server Performance

use the script jsmon.sh to monitor your Appserver processes

Thursday, January 19, 2006

Meta HTML Variables

Meta HTML variables are powerful options available in peoplecode that can be used in HTML areas to dynamically insert PeopleSoft environment variables and allow you to create pages that are dynamic. The following 2 are specially important.
%Image and %UserID.
If you want to refer to an image in your HTML Area, you can use %Image as follows
<img src='%Image(PSLOGO)'/> where PSLOGO is the name of image stored in peopesoft database image catalog. PeopleSoft will download this image file at runtime on the webserver and replace it with appropriate image url.
If you want to generate a url with currently logged in userid, %Userid can be used, instead of cookies, which may be problematic. For e.g.
<a href="http://www.xyz.com?userid=%userid">My Link</a>
Peoplesoft will automatically replace the %UserID with currently logged in user ID.
%URL(URLIdentifier [, NOENCODE | ENCODE | DESCR]) can be used to display the link stored in the URL Maintenance page.

Wednesday, January 04, 2006

How To Skip Search Dialog Box

Sometimes you want to suppress Search dialog box during the Page display. You must understand the default search dialog box processing and then determine what can you do to alter it programmatically.

  1. At Component Definition Level under Use Tab after Add Search record, there is a checkbox Force Search Processing. The Default is cleared which means do not display the search dialog box, if following conditions are true.
    a> All required keys have been provided (either by system defaults or by PeopleCode)
    b> If this an Add dialog box, then no "duplicate key error" results from the provided keys; if this error occurs, the processing resets to the default behavior.
    c> If this is a Search dialog box, then at least one row is returned based on the provided keys.

  2. If you select the Checkbox Force Search processing, then even if the above conditions are true, system will always display the search dialog box.

  3. To alter the search dialog box behaviour programmatically, irrespective of Component Definition Force Search Processing checkbox, You need to use SetSearchDialogBehavior(force_or_skip if possible) function in SearchInit event of the SearchRecord-> SearchField or at component level Search Record. for skip if possible use value 0 and for force display use value 1

  4. Another way of skipping the search dialog box is to use a search record which do not have any search keys defined for e.g Derived/Work record. It can be any record as long as it does not contain any fields with search key attribute selected in record field property.

Note: The above methods to skip search dialog box (except records with no search key) only works, if you meet the condition specified in 1 i.e. all the key items has values and it returns at least one row and no errors occur. You can use SetSearchDialogBehavior to override component level setting in searchinit event. Also see this Post for more info. SetSearchDialogBehavior

Tip: (8.4x tools rel only) To directly navigate to a page with portal navigation

Link: http://server/servlet_name/SiteName/PortalName/

For components content parameters are : Page=pagename&action=action_value&key_id=key_value

If you do not specify the Page then it goes to the first page in component.

Action = A - Add, U - Update/Display, L - Update/Display All, C - Correction

KeyIDs are actual Field Name of the Search record that has search key attribute set.

To display a page and navigate to that page for e.g URL Maintenance Page the URL will be


where psp - Portal servlet, use psc - content servlet to go to page without portal navigation

Sitename = ps, Portal Name = Employee, Node Name = EMPL and Content Type = C for Component,

Content ID = <menu>.<component>.<market>

Menu = UTILITIES, Component = URL_TABLE, Market = GBL


Key Value: CMDOCDB

This link will open directly the URL Maintenance page with URL Definition of CMDOCDB.