PeopleSoft Support & Tips

How to Use CTRL-J in Firefox

2011-09-19T17:16:00.001-04:00

When hitting Ctrl-J in Firefox, a new Firefox download window appears. In Microsoft Internet Explorer, hitting Ctrl-J shows PeopleSoft page information.

To view page information using Firefox browser, hold down the ctrl key and quickly hit "J" TWICE.

Also see the Resolution on My Oracle Support : E-PIA How to Use CTRL-J in Firefox [ID 756546.1]

How to Use CTRL+J in Google Chrome browser to display PeopleSoft Information

2011-09-02T10:54:00.001-04:00

Hi, I use chrome for most of my browsing needs. However, when I login to "Peoplesoft", I generally use a shortcut Ctrl+J to access some details that are specific to Peoplesoft. However, chrome has a predefined shortcut combo assigned with Ctrl+j. Does anyone know how to Disable it/Bypass it? On IE, pressing it twice will bypass the assigned shortcut. What's the procedure in Chorme?

Here is the solution, both works for me.

1. First press and hold J then press CTRL and release J.

2. Press CTRL+SHIFT+J Twice

For more details see the following link : http://www.google.com/support/chrome/bin/answer.py?answer=95655

Display Prompt on a Search dialog box

2011-09-01T17:12:00.001-04:00

If you have a prompt defined on a search record, it does not get displayed if you have component properties –> Internet tab set to use Basic Mode. (Default). Only Advanced mode displays the prompt on the search dialog box.

PeopleTools 8.50/8.51 AJAX issues

2011-08-19T09:58:00.001-04:00

After immediately upgrading to PeopleTools 8.50, we have encountered several issues that were related to new AJAX features introduced in 8.50. Here is the list of such issues

1. Getting frequent error 501 not implemented on the browser with IE7 and IE8.

2. Component page reset. Users are getting kicked out to first page or the search dialog box in the middle of a multi page transaction and losing the data they have entered.

3. Suddenly the page will become unresponsive and entire browser will freeze and will require restart of the browser.

All the 3 symptoms were annoying for the users and we eventually traced it to AJAX feature, that results in loss of POST data.

Turning off the AJAX setting resolved all 3 issues. Here is a way to turn off the AJAX

Go to PeopleTools –> Web Profile > Open the Web profile you use for e.g. DEV or PROD.

Go to Custom Properties and Add a Property AJAX of type Boolean and set its value to false. This will turn off the AJAX. To enable it again make it true or remove the row. Bounce all the Web servers using this web profile to make this setting take effect.

Though the above changes resolves the issue, this is not desirable and also may not be supported in future peopletools release. I have found this issue to be related to a bug in IE7 and IE8 when used with Windows XP OS. Fortunately microsoft includes the hotfix to resolve the issue, however registry change needs to be made to enable the hotfix. For more details, see this following KB : http://support.microsoft.com/kb/895954 - When you use Microsoft Internet Explorer or another ! program to perform a re-POST operation, only the header data is posted. See How to enable this hotfix section for registry changes.

Basically, the issue happens as IE sends the AJAX request in 2 tcp ip packets one for the header and one for the body. When network error happens, and ie tries to send the ajax request again, it fails to send the body. This causes PIA to loose the session state and we see the issues described above. The other browsers Firefox, Safari and Chrome do not send ajax request in multiple packets and this issue does not appear there.

Also see this article on further clarification on which registry key needs to be modified : http://www.geoffchappell.com/viewer.htm?doc=notes/windows/ie/featurecontrol.htm

According to this , you can specify the value in any one of the following location, in order of decreasing precedence.

HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Internet Explorer\Main\FeatureControl
HKEY_CURRENT_USER\Software\Policies\Microsoft\Internet Explorer\Main\FeatureControl
HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\FeatureControl
HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\Main\FeatureControl

Please leave comments, if applying the above registry changes resolves the issue.

PeopleSoft has also published this resolution on My Oracle Support (MOS) - E-PIA: PeopleTools 8.50 and 8.51 - Intermittent "Error 501--Not implemented" Messages when Navigating to a Page Using IE7 or IE8 Browser. Doc ID : 1337266.1

Determine WebLogic Version in PT 8.50

2011-06-15T12:14:00.001-04:00

cd $PS_HOME/webserv/peoplesoft/bin

. ./setEnv.sh

java weblogic.version

User does not see a Pagelet on a HomePage

2011-05-24T14:59:00.001-04:00

Even though user has a access to Pagelet, he does not see it on a HomePage. This indicates that WebServer Pagelet cache is corrupt. Only way to make it work correctly is to restart the webserver. But if this happens in the middle of a day, you can use this trick to force the webserver Homepage pagelet cache refresh.

Add a new page to Homepage tab as a required fix. (PeopleTools –> Portal –> Structure and Content –> Portal Objects –> HomePage –> Tabs – Home).

Go to the Tab Content Tab and add the new Pagelet as require fix and select the column, usually the last column. Save it and relogin with user id who is complaining that he is not able to see the pagelet. He should now see the missing pagelet. Remove the newly added pagelet, as it is no longer needed. You can use the following sql to determine what pagelet user has access to .

SELECT * FROM PSPRUHTABPGLT where oprid = '7420060' ORDER BY portal_col_num,portal_row_num;

For further reference, Go to MOS (My Oracle Support) and see the following resolution.

E-PORTAL: What Are The Tables Used By Portal Technology? [ID 651342.1]

Page data is inconsistent with database

2011-05-16T10:04:00.001-04:00

If you are getting this message, while saving the page, please refer to following resolution on MOS (My Oracle Support).

E-AS: How to Trace "Page data is inconsistent with database" error? [ID 655907.1]

Step 1: Set the following in psappsrv.cfg

TracePPR=1
TracePPRMask=32767

Step 2: Go to the PIA and before logging on, add &trace=Y (uppercase y) at the end of the web address and press the keyboard Enter key. Check the the first 5 SQL options and for PeopleCode the 'List Program' and 'Each statement' options.

Step 3: Log in to PIA and reproduce the error.

Step 4: Check the trace, especially the portion which contains this information:
PSAPPSRV.28630 1-167605 11.00.03 0.001 Record RO_LINE_ATTR.ROW_LASTMANT_DTTM database value "2007-06-11-10.58.44.000000" page value "2007-06-11-10.05.30.000000"
The trace should clearly show the field which is producing the error as well as the values populated both on database side and page side.

One of the most common reason for this happening is

You are using a view to select data into a Scroll based on a table and No Auto Select option is turned on. The number of columns in view and scroll area base table differs and additional column in base table contain not null values. You use Auto update for Scroll and change the value of the field in a scroll and save.

To resolve this issue, add the same number of columns to view as the base table or if possible, make the same view as a base record for the scroll that you use for selection.

Other reason is using SQLExec in SavePostChange to update the Base Table Record and therefore changing the value.

Prompt Values specified on Add Mode Search Record is not enforced when using ExcelToCI

2011-04-26T09:22:00.001-04:00

If you are creating Component interface by default it will not enforce the prompt values specified on Add search record and will let you input any values even though they are not valid. This is an issue when inserting new rows using ExcelToCI as it allows the user to enter invalid values without giving any error message.

To avoid this issue, open up the Add mode Search record for the component and go to Record field properties for the search fields and check Search Edit check box. This will enforce the use of valid values when adding a new row using Component interface used in ExcelToCI. By default Search Edit is not checked.

Here is the description of this field in PeopleBooks.

Search Edit Enabled only if Search Key is selected. Selecting this option enforces the required property and table edits on the search page. It also enforces these edits under circumstances where the search page would normally be bypassed. With this option, the user no longer has the ability to perform partial searches on this field.

Here is the MOS (My Oracle Support) Link that documents this issue.

E-CI: Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create [ID 664377.1]

Does Row Level Security Work in ExcelToCI? (Doc ID 972241.1)

As per the above resolution, row level security is not enforced using ExcelToCI.

Integration Gateway: General Connection Failed (158,10836)

2011-04-21T09:34:00.001-04:00

We send a XML Message to Vertex using HTTPTargetConnector in an app engine program in batch mode.
Occasionally we see that the Return response is the following : Integration Gateway: General Connection Failed (158,10836)

On seeing some other resolution the following could be the cause of this message.
This error is thrown when there is no valid response.

Possible errors include:
Bad gateway URL
Sync Service Timeout set and Service actually timed out.
Java exception thrown - Check Application Server for possible Java exception.
Since URL is correct and there is no Java exception, I think the reason is Sync Service Timeout set and Service actually timed out.

When I was reading PeopleBooks for HTTPTargetConnector Properties there is a Header Property called Timeout for which the default value is 50 seconds.

Here is the explanation from the peoplebook.
Specify the time in milliseconds for the connector to wait for the message to transmit. If the timeout period expires without a successful transmission, the transaction fails.
The default value is 50000 (50 seconds).

Setting the property value to 120000 (120 seconds or 2 minutes), resolved the issue. Make sure that you modify the Node Connector as well as routing connector properties if one is defined.

Navigation : PeopleTools –> Integration Broker –> Integration Setup –> Nodes –> Connectors

Property ID: HEADER

Property Name: TimeOut

Value: 120000

Go to Routings Tab and Select the Outbound Routing and Select Connector Properties. If you have a Property defined for e.g. PRIMARYURL then you will need to add it here as well, otherwise if it is blank then it is not needed to be added here.

Application Engine Program : PSPAL::Abort: Unexpected signal received

2011-04-07T11:19:00.001-04:00

When we create a Application engine program which refers to an State record, and migrate it to other environment without migrating the State record, it gets migrated successfully. Trying to run this program on the Batch Server gives the following error message.

Record XYZ not defined (108,505)
1341: 1301685557: PSPAL::Abort: Unexpected signal received
1341: 1301685557: PSPAL::Abort: Location: /vob/peopletools/src/pspal/exception_sigaction.cpp:492: RecoverableSignalHandler
1341: 1301685557: PSPAL::Abort: Generating process state report to /xxx/xxx/scripts/LOGS/psae.1341/process_state.txt
1341: 1301685557: PSPAL::Abort: Terminating process now.

in Windows
2564: 1301686035: PSPAL::Abort: Unrecoverable exception received
2564: 1301686035: PSPAL::Abort: Location: E:\pt85010b-retail\peopletools\SRC\pspal\exception_winunhandled.cpp:577: PSPAL::UnhandledExceptionFilter::RecoverableExceptionFilter
2564: 1301686035: PSPAL::Abort: Abort diagnostics generation is currently disabled
2564: 1301686035: PSPAL::Abort: Terminating process now.
PeopleTools 8.50.10 - Application Engine
Copyright (c) 1988-2011 PeopleSoft, Inc.
All Rights Reserved

Record XYZ not defined (108,505)
Message Set Number: 108
Message Number: 505
Message Reason: Record XYZ not defined (108,505)

Workaround
Copy the missing AET Record to the target database and it application engine program will work fine.

ExcelToCI Error occurred in routine sendSOAPRequest_SubmitToDB: The operation timed out.

2011-04-07T11:07:00.001-04:00

When trying to upload about 7000 rows using ExcelToCI for a custom component, users are getting the following error.

Error occurred in routine sendSOAPRequest_SubmitToDB:
Error:
Number: -2147012894
Description: The operation timed out

After doing my investigation, checking the web server and appserver access log I found the following issues, and here is what i did to resolve the issue. This is not documented anywhere. Please create a resolution if possible to help others.

1. Found that ExcelToCI uses SERVERXMLHTTP MSXML 6.0 object to send the SOAP request to the webserver,
   Set xHTTP = CreateObject("MSXML2.SERVERXMLHTTP.6.0")
   This request has a default 30 second timeout for receiving a packet of response data from the target server,
   See link : http://msdn.microsoft.com/en-us/library/ms760403 for more details on timeouts.

2. After checking the webserver access log (PIA_Access.log), determined that some of the POST request are taking about 59 seconds to complete and therefore we were receiving the error.

To resolve this issue, change the VB Macro code in ExcelToCI spreadsheet as follows

=======================================================

Add this line before xHTTP.send xDoc.xml in StagingAndSubmission Module under function sendSOAPRequest_SubmitToDB
Dim lResolve, lConnect, lSend, lReceive As Long
lResolve = 60 * CLng(1000)
lConnect = 90 * CLng(1000)
lSend = 90 * CLng(1000)
lReceive = 120 * CLng(1000)

xHTTP.setTimeouts lResolve, lConnect, lSend, lReceive
=======================================================

After changing this value, the ExcelToCI was still failing. Once again I checked the web Server and App server log and found that after loading about 50 to 60 rows, request is taking more than 5 minutes to respond and appserver is killing the appserv thread and therefore error was received saying that The Operation time out.

I had two options
1. Increase the XMLHTTP timeout and also increase the appserver timeout or find out why a request is taking more than 5 minutes to complete. Used Precise i3 performance monitoring tool and also the live Oracle
session and determined the following issue.
1. CI was firing sql to fetch location code description using a view that was not correctly joined with other large table using location code.
2. A Save Edit Peoplecode was written at Level 2 to fetch some data from the Oracle table using SQL Exec. This was causing this sql to fire 160,000 times as it was firing for every row in the scroll as new rows are being added.

Following improved the performance.
1. Remove the related display field from the page. Alternative option is to tune the sql for related display field.
2. Moved the save edit code to field edit, so that it fires only for the newly inserted rows. Other option is to write conditional logic to see if row is changed.

After these, 2 changes, the process finished inserting 7100 rows in 23 minutes. Only two request exceeded default timeout. One took 32 and other 35 seconds to respond.

I have also noticed, that if Chunking factor is reduced to 1 to have smaller number of rows processed, PSAPPSRV is restarting due to recycle count of 5000 being reached, and this results in a login Error and HTML (invalid XML) data is being sent to the Excel. Is this the expected behavior? Only solution to this issue is to increase the recycle count temporarily and change it back to original. Fortunately this parameter is dynamic and does not require restart of the appserver.

Changing the Userid when triggering the subscription peoplecode.

2011-03-21T12:38:00.003-04:00

You can use the following IntBroker Class method : SwitchAsyncEventUserContext to change the context of the peoplecode run inside subscription peoplecode. This is to be used by IB only (there are checks to make sure that is the case) and can only be used for IB events that are fired asynchronously (OnRoute, OnSend, OnNotification, etc..). One use case is if you are submitting a process request from a self service user id and you do not want to give access to query security to each user who is triggering the message. This method is added in PT 8.50 and not available for lower tools release.

For more info use : E-IB: User Security required on target db for async messages in 8.48.0x [ID 654592.1]

PeopleBook definition of this method is as follows.

SwitchAsyncEventUserContext

Syntax
SwitchAsyncEventUserContext(UserID, LanguageCode)
Description
Use the SwitchAsyncEventUserContext method to switch the user context within an Integration Broker asynchronous event.
Parameters
UserID
Specify the user ID, as a string, to which you want to switch the context.
LanguageCode
Specify the language code, as a string, for the user ID.
Returns
A Boolean value: true if the switch user was successful, false otherwise.
Example
&returnValue = %IntBroker.SwitchAsyncEventUserContext("VP1", "ENG");

Which Run Status values are considered "Active" by the Process Scheduler

2011-02-03T10:02:00.001-05:00

Question: Which Run Status values are considered "Active" by the Process Scheduler ?
Answer: The Process Scheduler considers: Initiated, Blocked, and Processing as active slots.

Application Run Controls versus Process Run Controls

2011-02-02T12:27:00.001-05:00

Please see this on Oracle support Site : (http://support.oracle.com or http://supporthtml.oracle.com for non flash based browsers.

E-PRCS: Master Note: Process Scheduler [ID 1266607.1]

Application Run Controls versus Process Run Controls
There are two types of "Run Control" stored in different database tables.

Process Run Control (created by the system)
Application Run Control ( created by the Application)

Process Run Control
Process Run Controls are created automatically by the system when a user creates a process request. The process run controls are stored in multiple tables. User ID and Run Control ID are keys to each tables:
PS_PRCSRUNCNTL - Stores user's language code and language option
PSPRCSRUNCNTLS - Stores server name and time zone
PS_PRCSRUNCNTLEOPT - Stores parameters for process request runs to email such as email address, subject, body text…etc.
PS_PRCSRUNCNTLDTL - Stores run control details such as output type, output format, output destination, folder name, email output option for per process type and process name
PS_PRCSRUNCNTLDIST - Stores distribution list

Application Run Control
Application Run Controls are created by user when he/she submits a process to run. It holds parameters required and specific to the process/program. When the Application Run Control is created, it triggers the system to create the Process Run Controls as well using the same Run Control ID as the key.

From the end-user perspective, there's no distinction between Process and Application Run Controls. The Process Run Control is assigned the exact same name/id as the Application Run Control.
However, it's important for application developers to distinguish between the two when creating new application process/program.
Process run control and application run control are stored in different tables.
PeopleTools delivers the table to store only the process run control.
Application developer is responsible for creating the application run control table to store the application run control information.
If a user creates a new application run control to run a report, then the corresponding process run control will be given the same name.

Run Control information is stored in two PeopleTools tables (at least):
PSPRCSRUNCNTLS (stores information from the Process Scheduler request panel) and
PS_PRCSRUNCNTL (stores the language code),
and at least one Application Run Control Table.

Note: From 8.4x PSPRCSRUNCNTLS is introduced which is used in place of PSPRCSRUNCNTL.

Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create

2010-12-09T08:58:00.001-05:00

Here is the description of this field in PeopleBooks.

See the Resolution

E-CI: Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create [ID 664377.1]

If you want the partial search for this field to be enabled, then do the following.

Add the Search field as a read/write property in CI.

SQLExec : Return: 8015 - Bind value is too long

2010-12-09T08:39:00.001-05:00

You get this error in an online page or while running a Application engine program. This error happens when you try to insert more than 254 characters in a long field using sqlexec and do not use %TextIn meta sql.

Resolution

Use %TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g. %TextIn(:1)

%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms.

Here are some resolutions that discusses this issue in Metalink – Oracle support site.

E-AE Application Engine PeopleCode Step with SQLExec Receives Error; return code 8015 "Bind value is too long" [ID 889806.1]

E-PC:"Bind value is too long" Error When Using SQLExec to Insert into Long Char Field [ID 620874.1]

Portal Content Reference Navigation Path

2010-12-03T10:58:00.001-05:00

Very often you need to find out what is the Navigation Path for a given component name in PeopleSoft portal or HRMS or Financials or CRM?

If you are using 8.50 and 9.1 then you can find this information using Enterprise Components –> Find Object Navigation. You can search by component name, page name, Secondary Page name or Content Reference Name. Please note that you must be using application version 9.1 or higher. Just upgrading peopletools to 8.50 or higher will not enable this functionality.

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2,
                                              l_portal_reftype VARCHAR2,
                                              l_portal_objname VARCHAR2,
                                              l_level          NUMBER,
                                              l_type           VARCHAR2,
l_count_max      INTEGER DEFAULT 10)
RETURN VARCHAR2
IS
pl_count              INTEGER := 0;
pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname;
pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0;
pl_portal_label       psprsmdefn.portal_label%TYPE := ' ';
pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' ';
CURSOR cur_1 IS
    SELECT portal_prntobjname,
           portal_label,
           portal_seq_num
    FROM   psprsmdefn
    WHERE portal_name = l_portal_name
           AND portal_reftype = l_portal_reftype
           AND portal_objname = pl_portal_objname;
BEGIN
WHILE pl_count <> l_level LOOP
      pl_count := pl_count + 1;

      EXIT WHEN pl_count > l_count_max;

      OPEN cur_1;

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num
      ;

      IF cur_1%found THEN
        pl_portal_objname := pl_portal_prntobjname;
      ELSE
        pl_portal_label := ' ';

        pl_portal_seq_num := 0;

        EXIT WHEN cur_1%notfound;
      END IF;

      CLOSE cur_1;
END LOOP;

IF l_type = 'S' THEN
    RETURN pl_portal_seq_num;
ELSE
    RETURN pl_portal_label;
END IF;
END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname,
       a.portal_linkobjname,
       a.portal_seq_num                              seq,
       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')
             ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L')
       || Decode(a.portal_label, ' ', '',
                                 ' > '
                                 || a.portal_label)) navigation,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7,
       a.portal_uri_seg1                             menuname,
       a.portal_uri_seg2                             component,
       a.portal_uri_seg3                             market,
       a.portal_urltext
FROM   psprsmdefn a
WHERE a.portal_name = 'EMPLOYEE'
       AND a.portal_reftype = 'C'
       AND a.portal_prntobjname <> ' '
--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME'
ORDER BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S')
          ||
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')

Application Engine program remains in processing status forever.

2010-11-13T13:46:00.001-05:00

Process Scheduler Server Agent PSUNX is below the Log Space Threshold
The Log/Output Directory /xyz/psft/pt/8.50/appserv/prcs/xyz/log_output for the Process Scheduler Server Agent PSUNX
in server xyz.com for database xyz has 8 MB of free space.
This is below the disk threshold value of 10 MB in the Log Space Threshold
found in the Process Scheduler Configuration file. The system is suspending
the server agent until more disk becomes available. Until then, no queued
process requests will be processed in this Process Scheduler Server Agent.

This happened because, one of the user modified the Application engine Process definition, override options as follows and ran the process using process scheduler.

-debug Y –trace 3

This resulted in a trace file that keeps growing very fast and consumes the entire available disk space on the appserver.

Removing the above parameters (-debug Y) and deleting the huge log file resolved the issue.

Following sql can be used to determine if there are any app engines that have this defined.

SELECT lastupdoprid,
       lastupddttm,
       prcsname,
       parmlist,
       descrlong
FROM   ps_prcsdefn
WHERE parmlist <> ' '
       AND prcstype = 'Application Engine'
       AND prcsname <> 'PSCONQRS';

-debug Y should only be used from psae command line for interactive debugging. It should not be used in Process definition.

Scheduled Crystal Process remains in initiated status when the account is locked

2010-11-13T13:30:00.001-05:00

If you have implemented password controls that locks the users account after x no of invalid attempts and the user has a crystal report scheduled, it makes crystal report process to remain in initiated status forever. It also generates an ever increasing log file, which has a potential of consuming entire available disk space and disrupting the other batch processes.

Following Trace files are generated.
CRW_XRFWIN_12345.log
pssqltrace[1].trc

It appears that Crystal Report repeatedly calls the database sql statements and never comes out of it.

Steps to reproduce the issue.
1. Create a testid testps and assign roles PeopleTools and PeopleSoft User.
2. Schedule XRFWIN Crystal Report to run within next 5 minutes.
3. Update the testps user profile and lock the account.
4. Go back to process monitor and observe that process remains in initiated status.
Also if you go back to server and check the log_output folder for xrfwin you will see that log file and trace file size keeps on increasing.

Only workaround is to Run a SQL to detect this situation and cancel the process. Unlocking the user account also fixes the issue. The fix is targeted in next Tools release 8.51. We observed this behavior in only PT 8.50 and Crystal Report 2008 SP1. We are currently using patch 8.50.10.

SQL to determine the Processes in initiated status.

SELECT 'Processes in Initiated Status ',
       prcsinstance,
       oprid,
       prcsname,
       servernamerqst,
       servernamerun,
       rundttm,
       lastupddttm
FROM   psprcsrqst
WHERE runstatus = 6
       AND ( SYSDATE - Cast(lastupddttm AS DATE) ) * 24 * 60 > 15

SQL to determine Scheduled Processes and user account is locked.

SELECT b.prcsinstance,
       b.prcstype,
       b.prcsname,
       (SELECT e.descr
        FROM   ps_prcsdefn e
        WHERE e.prcstype = b.prcstype
               AND e.prcsname = b.prcsname)     descr,
       b.oprid,
       (SELECT a.oprdefndesc
        FROM   psoprdefn a
        WHERE a.oprid = b.oprid)               NAME,
       runcntlid,
       (SELECT f.qryname
               || ', '
               || f.descr
        FROM   ps_query_run_cntrl f
        WHERE f.oprid = b.oprid
               AND f.run_cntl_id = b.runcntlid) qryname,
       recurname,
       runstatus,
       (SELECT xlatshortname
        FROM   psxlatitem c
        WHERE c.fieldname = 'RUNSTATUS'
               AND c.fieldvalue = b.runstatus) rundescr,
       diststatus,
       (SELECT d.xlatshortname
        FROM   psxlatitem d
        WHERE d.fieldname = 'DISTSTATUS'
               AND d.fieldvalue = b.diststatus) distdescr,
       pt_retentiondays,
       rundttm,
       rqstdttm,
       b.lastupddttm,
       servernamerqst
FROM   psprcsrqst b,
       psoprdefn z
WHERE b.runstatus IN ( 5 )
       AND b.oprid = z.oprid
       AND z.acctlock = 1
ORDER BY rundttm DESC

You are not authorized to run process type XRFWIN and process name Crystal. (65,8)

2010-11-13T13:14:00.001-05:00

This happens if the user’s Primary Permission List or Process Profile Permission list is blank and user is trying to submit a Process request using Process Scheduler.

To fix this, make sure that user profile has valid primary permission list and Process profile permission list defined.

You can use the following sql to find out which users does not have primary permission list or process profile permission list.

Primary Permission List SQL

select * from psoprdefn where oprclass = ' ';

Process Profile Permission List SQL
select * from psoprdefn where prcsprflcls = ' ';

You get the following Message if Primary permission list is blank.

---------------------------

Windows Internet Explorer

---------------------------

You are not authorized to run process type XRFWIN and process name Crystal. (65,8) PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldFormula Name:LaunchAndRunProcessRequest PCPC:97211 Statement:1113

Called from:PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldFormula Name:LaunchProcessRequestDlg Statement:1133

Called from:PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldChange Statement:1

You must be specifically authorized to run this process. Authorization is granted using the Process Definition table and Maintain Security

---------------------------

You get the following message, if the Process Profile Permission list is blank.

Windows Internet Explorer

---------------------------

Process Profile defined for user ID %2 is invalid (65,111)

The process profile assigned to the user ID is either not valid or blank. Update the User Profile for the user ID in the Maintain Security component with a valid process profile.

---------------------------

You also get this message in addition to above.

E-PRCS: PeopleTools 8.50 After DST change processes run one hour ahead/behind scheduled time

2010-11-13T13:07:00.001-05:00

After the recent DST time change in USA on November 7th 2010, Scheduled processes are running one hour behind in our case. Our base time zone is EST. This happens due to a bug in the current Tools version less than 8.50.13. This issue is fixed in 8.50.13 and 8.51.

Workaround

To resolve this issue, restart all your batch server domains in DEV/QA/PROD. Both NT and unix server domains are affected by this issue.

For more detail see : E-PRCS: 8.50 After DST change processes run one hour ahead/behind scheduled time (Doc ID 1265111.1)

References NOTE:1163113.1 - E-PRCS Processes Are in "Queued" Status when Process Scheduler Resides in a Different Time Zone

Root Cause

This happens due to inability of process scheduler to detect change in sessiontimezone after the DST change and it’s use of the following SQL in 8.50

select ..... from PSPRCSQUE........where RUNDTTM <= SYSTIMESTAMP

By understanding the Oracle DBMS concept of SYSTIMESTAMP, the session time zone also takes effect when a TIMESTAMP value is converted to the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE datatype.

Therefore, for this conversion to take place : RUNDTTM <= SYSTIMESTAMP, the user's session timezone is returned and is not the same timezone as the database server.

Note: This only impacts those customers using Oracle and PT 8.50.12 or less. It does not impact non Oracle or lower tools release customers.

E-QR: Wrong Output Is Generated When Running PSQuery Using Email Type

2010-10-04T16:08:00.001-04:00

If you have files in the following folder $PS_CFG_HOME/appserv/prcs/<dbname>/files with extension xls, cxv , pdf and you try to schedule a query using Schedule Query (PSQUERY), and select email option, you may get incorrect file emailed to you.

Workaround: Delete the files in $PS_CFG_HOME/appserv/prcs/<dbname>/files folder that has extension xls, csv or pdf.

This is a bug introduced in 8.50.08 and as of 8.50.12 not yet resolved.

See My Oracle Support resolution ID: 1186374.1

PS resolution says this happens when you have PS_FILEDIR is setup in the Process scheduler box, but this was not the case in our environment.

Error getting report repository location. (63,85)

2010-09-29T10:06:00.001-04:00

If you are getting this message when running a new Report, and all the configuration settings are correct then the issue is due to incorrect initialization of psreports or SchedulerTransfer servlet. This issue was resolved in 8.48.16 but appeared again in 8.50.10. To fix this simply do the following.

From the IE browser, just paste the following URL.

If using NON SSL

http://xyz.com:port/psreports/ps/

if using SSL

https://xyz.com:port/psreports/ps/

Replace xyz.com with the actual Report repository web server name and port with actual port number used. Also if you use non default site, then replace ps with the actual pia site name.

See the Metalink resolution :

E-RD: Reports do not post initially when the Web Server is bounced [Video] [ID 660253.1]

Other Option is to open a previously successful report that invokes the psreports servlet.

This could be specific to WebLogic version and Patch applied.

WebLogic 10.3.2 32 Bit on Red Hat Linux 5.3 64 bit

JrockIT JRE

java version "1.6.0_17"

Java(TM) SE Runtime Environment (build 1.6.0_17-b04)

BEA JRockit(R) (build R27.6.6-28_o-125824-1.6.0_17-20091214-2104-linux-ia32, compiled mode)

PeopleTools : 8.50.10

All the patches applied using WebLogic Smart Update Utility: bsu

PeopleTools 8.50 Known Issues - Master Note [ID 1114793.1]

2010-09-28T12:22:00.001-04:00

Oracle has posted a Master Resolution on Metalink (Flash based: http://support.oracle.com) or Non Flash based (http://supporthtml.oracle.com).

Resolution ID: 1114793.1

It has broken the incidents into following categories.

Application Development
Install/Updates
Integration Tools
Reporting Tools
Server Tools
Open Incidents (ALL components)

This document will be updated frequently. Please check this document to see if your issue is already listed in it.

List of Batch Processes in queued status

2010-09-15T16:55:00.001-04:00

The following sql will generate a list of batch processes in queued status in Process Monitor.

SELECT b.prcsinstance,
       b.prcstype,
       b.prcsname,
       (SELECT e.descr
        FROM   ps_prcsdefn e
        WHERE e.prcstype = b.prcstype
               AND e.prcsname = b.prcsname) descr,
       b.oprid,
       (SELECT a.oprdefndesc
        FROM   psoprdefn a
        WHERE a.oprid = b.oprid) NAME,
       runcntlid,
       (SELECT f.qryname
               || ', '
               || f.descr
        FROM   ps_query_run_cntrl f
        WHERE f.oprid = b.oprid
               AND f.run_cntl_id = b.runcntlid) qryname,
       recurname,
       runstatus,
       (SELECT xlatshortname
        FROM   psxlatitem c
        WHERE c.fieldname = 'RUNSTATUS'
               AND c.fieldvalue = b.runstatus) rundescr,
       diststatus,
       (SELECT d.xlatshortname
        FROM   psxlatitem d
        WHERE d.fieldname = 'DISTSTATUS'
               AND d.fieldvalue = b.diststatus) distdescr,
       pt_retentiondays,
       rundttm,
       rqstdttm,
       lastupddttm,
       servernamerqst
FROM   psprcsrqst b
WHERE runstatus IN ( 5 )
ORDER BY rundttm DESC

Invalid signon time for user PS@xyx.com

2010-09-15T16:51:00.001-04:00

when user tries to signon they get the following message on the Login Page.

Invalid signon time for user

This message is thrown due to a blank classid row in PSROLECLASS table. Use the following SQL to determine the Cause.
select * from psroleclass where classid = ' '

To fix this, run this SQL.
delete from psroleclass where classid = ' '

No Appserver or Webserver bounce was required.

This was tested in 8.50.10

PeopleTools 8.4x/8.1 ERD Diagram for Security tables

2010-03-25T16:38:00.001-04:00

On Metalink Go to Knowledgebase article : 611947.1 : PeopleTools 8.x Security Relationship Diagrams

The above document provides the ERD diagram for peopletools security table.

Run the PeopleTools 8.50 Installation in GUI mode for Linux/Unix servers

2010-01-21T18:34:00.001-05:00

In PeopleTools 8.50, PeopleSoft does not document the command line parameter needed to run the installation in GUI mode for Linux/Unix servers. The installation defaults to console mode. To get the same GUI experience, you can use the following command line flag.

-i swing

So of you have downloaded the PeopleTools installation media to PS_INSTALL, you can run the following.

PS_INSTALL/Disk1/setup.sh –tempdir $HOME/tmp –i swing

(Note tempdir is a documented command line parameter to use the folder other than /tmp. It requires at least 2GB free space in temp folder).

--------------------------------------------------------------------------------------------------

Usage: setup [-f <path_to_installer_properties_file> | -options]
(to execute the installer)

where options include:
    -?
            show this help text
    -i [swing | console | silent]
            specify the user interface mode for the installer
    -D<name>=<value>
            specify installer properties

--------------------------------------------------------------------------------------------------

I will be curious, if there is a way to do silent install by providing all the needed input in a properties file. I do not see this documented any where.

Note: This method applies to other installation type for e.g. Verity Install.

Trying to Schedule Query gives message "An error has occurred" and kicks the user back to an Error page.

2009-11-17T15:16:00.001-05:00

I have noticed the following issue on PeopleTools 8.48.16.

Go to Reporting Tools -> Query -> Schedule Query -> Add a new Value –> TEST
Enter the query name : ERROR_QUERY and tab out of the field.
This displays the run time prompt page. Enter the value and hit OK or CANCEL
Page shows flashing Processing image and displays the page "An error has occurred" eventually. Appserver log shows that a core file is generated.

FIX

To fix the issue, open the query in Query Manager from PIA. Change the query properties –> Description and resave the query. This fixes the issue.

Could not sign on to database xxxx with user yyyy for app engine program

2009-10-09T17:22:00.001-04:00

When trying to run appengine program from command line (psae), you are getting this message

“Could not sign on to database xxxx with user yyyy for app engine program”

One possible reason is that psae needs database name in upper case. If you are using lower case name then the above message appears.

Also PS_SERVER_CFG must contain the fully qualified name of a correctly configured Process Scheduler PSPRCS.CFG file

For e.g.

PS_SERVER_CFG=$PS_HOME/appserv/prcs/<domainname>/psprcs.cfg;export PS_SERVER_CFG

psae -CT ORACLE -CD dbname-CO userid -CP password -R runcontrolid -I 0 -AI <appengine progname>

Also When PeopleSoft Application Engine runs from the command line, it resolves %PS_SERVDIR% to the value of the environment variable PS_SERVDIR instead of the parent directory of a Process Scheduler configuration.

An error has occurred that has stopped this transaction from continuing.

2009-09-14T17:49:00.001-04:00

The above error may happen when doing the following in Enterprise Portal:

Navigation: Portal Administration -> Branding -> Define Headers or Define Footers (Add / View existing Value)

Add a New Value or Find An existing Value

Set Header ID: TEST1

You will get this message "An error has occurred that has stopped this transaction from continuing."

Reason
This message appears in Enterprise Portal due to 0 rows in PS_INSTALLATION Table. To fix this issue: Insert a row in PS_INSTALLATION table and restart the appservers.

The SQL I have used is as follows.

INSERT INTO PS_INSTALLATION
VALUES('Y','Y','N','N','USA','USD','OFFIC','N',25,'N','N');

Tested this with Enterprise Portal 8.8 and PeopleTools 8.48.16

Validate Userid and password against LDAP directories Using PeopleCode

2009-01-15T11:11:00.001-05:00

Following peoplecode is tested in 8.48.16 and assuming that you are using Oracle Wallet and LDAP libraries for connecting to ldap server. (Tested on HP*UX 11.11 server). It also assumes you have LDAP Port, Server name, Default Connect DN defined in PeopleTools -> Security -> Directory -> Directory Configuration. LDAPS Port is optional but highly recommended. This can be used in following scenarios.

You are logged in PeopleSoft with generic id and you need to validate user's password against ldap directory. Or you want to see programmatically if the user exist in LDAP with a valid password.

Create this as Class to Application Package: XX_UTILS

class LDAP
   method ValidatePassword(&userid As string, &pwd As string, &directory_id As string) Returns boolean;
end-class;

method ValidatePassword
   /+ &userid as String, +/
   /+ &pwd as String, +/
   /+ &directory_id as String +/
   /+ Returns Boolean +/
   Local string &defaultDN, &dn, &outDN, &server, &SSL;
   Local integer &port, &nonsslport, &sslport, &EXECRSLT, &start, &num_chars, &ret;
   Local Interlink &LDAP_BIND;
   Local BIDocs &rootInDoc, &rootOutDoc;
   
   SQLExec("select a.DSCNCTDN, b.DSSRVR, b.LDAPPORT, b.ldapsport  from PSDSDIR a, PSDSSRVR b where a.DSDIRID = :1 and a.DSDIRID = b.DSDIRID", &directory_id, &defaultDN, &server, &nonsslport, &sslport);
   If All(&sslport) Then
      &SSL = "YES";
      &port = &sslport;
   Else
      &SSL = "NO";
      &port = &nonsslport;
   End-If;
   
   If All(&server, &port, &defaultDN) Then
      
      &LDAP_BIND = GetInterlink(Interlink.LDAP_BIND);
      &LDAP_BIND.UserID_Attribute_Name = "uid";
      &LDAP_BIND.URL = "file://psio_dir.dll";
      &LDAP_BIND.BIDocValidating = "Off";
      &LDAP_BIND.SSL = &SSL;
      REM &LDAP_BIND.SSL_DB = "e:\certs\cert7.db";
      &start = 5;
      &num_chars = Find(",", &defaultDN) - &start;
      &dn = Replace(&defaultDN, &start, &num_chars, &userid);
      &rootInDoc = &LDAP_BIND.GetInputDocs("");
      &ret = &rootInDoc.AddValue("Server", &server);
      &ret = &rootInDoc.AddValue("Port", &port);
      &ret = &rootInDoc.AddValue("Distinguished_Name", &dn);
      &ret = &rootInDoc.AddValue("User_Password", &pwd);
      &ret = &rootInDoc.AddValue("Encrypted", "NO");
      
      &EXECRSLT = &LDAP_BIND.Execute();
      If (&EXECRSLT = 1) Then
         &rootOutDoc = &LDAP_BIND.GetOutputDocs("");
         &ret = &rootOutDoc.GetValue("Distinguished_Name", &outDN);
         If &outDN = &dn Then
            Return True;
         End-If;
      End-If;
   End-If;
   
   Return False;
end-method;

Sample code to call the function.

import XX_UTILS:LDAP;

Local XX_UTILS:LDAP &ldap = create XX_UTILS:LDAP();
Local boolean &return;

&userid = "userid"
&pwd = "xxxx";
&directory_id = "LDAP"; 
&return = &ldap.ValidatePassword(&userid, &pswd, &directory_id);
 If &return Then
    /* success */
else
   /* failure */
end-if;

Identify List of users who has minimized the Menu Pagelet

2008-10-16T11:55:00.001-04:00

Sometimes user Minimizes the Menu Pagelet on PeopleSoft HRMS HomePage and do not know How to maximize it. Here is a query that can identify list of users who has minimized the Menu Pagelet.

PeopleSoft stores this information in PSPRUHTABPGLT Record.

It has a column PORTAL_MINIMIZE which has 2 values 0 (Maximize) and 1 (Minimize).

-- List of people who have maximized/minimized their Menu Pagelet
--PORTAL_MINIMIZE = 0 (Maximize)

select * from PSPRUHTABPGLT where PORTAL_OBJNAME_PGT = 'MENU' and PORTAL_MINIMIZE = 0
select b.oprid,b.oprdefndesc,b.lastsignondttm from PSPRUHTABPGLT a, PSOPRDEFN B where a.PORTAL_OBJNAME_PGT = 'MENU' and a.PORTAL_MINIMIZE = 0 and a.oprid = b.oprid

--PORTAL_MINIMIZE = 1 (Minimize)
select * from PSPRUHTABPGLT where PORTAL_OBJNAME_PGT = 'MENU' and PORTAL_MINIMIZE = 1
select b.oprid,b.oprdefndesc,b.lastsignondttm from PSPRUHTABPGLT a, PSOPRDEFN B where a.PORTAL_OBJNAME_PGT = 'MENU' and a.PORTAL_MINIMIZE = 1 and a.oprid = b.oprid

If there is no entry in this table, then it is always Maximized.

Here is a screenshot of How the minimized Menu Screen looks like.

Configuration Settings for Tracing

2008-07-21T14:55:00.001-04:00

Online Process

Set the following in psappsrv.cfg, or for a single user session in PIA using trace=y

“31” is the recommended value when tracing with “TraceSQL”.
“1984” is the recommended value when tracing with “TracePC”.

Batch Process

Application Engine

"135" is the recommended value when tracing with "TraceAE"

if peoplecode tracing is needed, please also set

TraceSQL= 31

TracePC = 1984

You can also do this in process definition of Application Engine to affect this for only 1 program.

Override options : Append : -TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 1984

To see which processes are set with this options, run this sql.

SELECT PRCSNAME, PARMLIST FROM PS_PRCSDEFN WHERE UPPER(PARMLIST) LIKE '%TRACE%' AND PRCSTYPE = 'Application Engine';

To generate Database Level Trace, Use TraceAE=2183. This will create file within the "UDUMP" directory on the database server.

It will contain details of each SQL statement that was executed on the database including its runtime execution plan. You can then use this as a input to tkprof for generating formatted trace report. However this will not capture bind variables. To capture bind variables, following trigger is needed.

CREATE OR REPLACE TRIGGER MYDB.SET_TRACE_POCALC

BEFORE UPDATE OF RUNSTATUS ON MYDB.PSPRCSRQST

FOR EACH ROW

WHEN ( NEW.runstatus = 7

AND OLD.runstatus != 7

AND NEW.prcstype = 'Application Engine'

AND NEW.prcsname = 'PO_PO_CALC'

)

BEGIN

EXECUTE IMMEDIATE

'ALTER SESSION SET TIMED_STATISTICS = TRUE';

EXECUTE IMMEDIATE

'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED';

EXECUTE IMMEDIATE

'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''POCALC''';

EXECUTE IMMEDIATE

'ALTER SESSION SET EVENTS = ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

END;

Cobol Process

TraceSQL=128

SQR Process

A database level trigger is the only way for generating SQR trace.

Sample trigger script.

CREATE OR REPLACE TRIGGER MYDB.SET_TRACE_INS6000

BEFORE UPDATE OF RUNSTATUS ON MYDB.PSPRCSRQST

FOR EACH ROW

WHEN ( NEW.runstatus = 7

AND OLD.runstatus != 7

AND NEW.prcstype = 'SQR REPORT'

AND NEW.prcsname = 'INS6000'

)

BEGIN

EXECUTE IMMEDIATE

'ALTER SESSION SET TIMED_STATISTICS = TRUE';
EXECUTE IMMEDIATE

'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED';
EXECUTE IMMEDIATE

'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''INS6000''';
EXECUTE IMMEDIATE

'ALTER SESSION SET EVENTS = ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

END;

Once the raw database trace is captured, execute the program “tkprof” with following sort options:

tkprof <trace_input_file> <rpt_output_file> sys=no explain=<user_id>/<password> sort=exeela,fchela,prscpu,execpu,fchcpu

For more information, Read peoplesoft resolution 201049233: E-ORACLE:10g Master Performance Solution for Oracle 10g and download the red paper attached to it.

Synchronous App Message Error Status SQL

2008-07-03T14:45:00.001-04:00

If you have turned on Logging in your Service Operations -> Routings, you can run these sql statements to get the Error details.

select * from psibloghdr where STATUSSTRING = 'ERROR' order by PUBLISHTIMESTAMP desc

select * from psiblogerr order by ERRORTIMESTAMP desc

select * from psiblogerrp order by ERRORTIMESTAMP desc

To see the explanation for message no., Run

select * from PSMSGCATDEFN where message_set_nbr = 158 and message_nbr = :1

You can see this information from online page

PeopleTools -> Integration Broker -> Service Operations Monitor -> Synchronous Services

Application Engine Aborts if it has state record that is not present in the Database.

2008-06-30T12:07:00.001-04:00

If you have a application engine program that has define a state record and it is not defined in the App designer it causes core dump when trying to run the program. This can happen if you have migrated a app engine program from DEV to other environment and forgot to add the state record to project. Here is a query that can identify all such app engine programs.

select * from PSAEAPPLSTATE a where not exists (select 'x' from psrecdefn b where a.AE_STATE_RECNAME = b.recname).

This was observed in PT 8.48.16 and running the App engine program using command line on HP*UX 11.11 server. Database: Oracle 9.2.0.8 64 bit on HP*UX.

Here is the actual error message.

Executing PS Application Engine XXXX program which will use run control XXXX
aCC runtime: pure virtual function called for class "IPSRecBuf".
24493: 1214837730: PSPAL::Abort: Unrecoverable signal received
24493: 1214837730: PSPAL::Abort: Location: /vob/peopletools/src/pspal/exception_sigaction.cpp:553: UnrecoverableSignalHandler
24493: 1214837730: PSPAL::Abort: Generating process state report to /xxxx/xxxx/xxxx/LOGS/psae.24493/process_state.txt
24493: 1214837730: PSPAL::Abort: Terminating process now.
./xxxx.sh[67]: 24493 Abort(coredump)

Keyword: IPSRecBuf

Schedule Query Security Access

2008-04-11T14:37:00.001-04:00

Access to Schedule Query is controlled by the following.

1. Schedule Query Component
2. Access to PSQUERY App engine process.

There are 2 ways you can run schedule Query process.

1. From Query Manager or Query viewer: These pages uses SCHED_QUERY_QRYVW (SCHEDQUERY2) Component with different search record: QUERY_RUN_QRYVW
2. From Schedule Query: This page uses SCHED_QUERY (SCHEDQUERY) Component with search record: QUERY_RUN_CNTRL

Following Process Groups are assigned to the PSQUERY App engine process that is used for scheduling queries.
TLSALL

select * from PS_PRCSDEFNGRP where prcsname = 'PSQUERY'

Therefore to Grant access to all users for Scheduling Queries who have access to Query Manager or Query Viewer Component following must be done.

1. Add access to Component : SCHED_QUERY (Baritemname: SCHEDQUERY) and SCHED_QUERY_QRYVW (Baritemname: SCHEDQUERY2)
2. Add Process groups to Same permission list that grants access to Schedule query: TLSALL

In Demo, PTPT1000 Classid and Role PeopleSoft user has access to the components

SCHED_QUERY (Baritemname: SCHEDQUERY) and SCHED_QUERY_QRYVW (Baritemname: SCHEDQUERY2)

In Demo, PTPT1200 Classid and Role PeopleTools has access to TLSALL Process group which can run PSQUERY Process.

Therefore, user must have access to PTPT1000 and PTPT1200 permission list. Therefore Role PeopleTools and PeopleSoft user must be assigned to successfully run the Schedule Query process using delivered Pages.

If you are creating a custom permission list, you can assign access to these components and process group to same permission list. Assign the permission list to a role and assign it to actual user.

Your users may get this error message, if they do not have access to process group assigned to PSQUERY Process and try to schedule the query.

PeopleSoft error report: Error: Required ProcessRequest attribute missing: JobName (65,151) PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldFormula Name:LaunchAndRunProcessRequest PCPC:67779 Statement:768 Called from:PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldFormula Name:LaunchProcessRequestDlg Statement:787 Called from:QUERY_RUN_CNTRL.QRYNAME.SavePreChange Statement:2

This error normally means user is not having access to Job or process group assigned to the process or job name. You can use these queries to determine what process groups are assigned to a job or process definition and what permission list has access to it.

select * from PS_PRCSDEFNGRP where prcsname = 'PSQUERY'
select * from PS_PRCSJOBGRP where PRCSJOBNAME = '3CBL'
select * from psauthprcs where prcsgrp = 'TLSALL'

Retrieve milliseconds from Oracle database and display it on a page.

2008-04-03T17:35:00.001-04:00

You can use the following SQL to get milliseconds from Oracle 9i and higher and assign it to a Field of Type Time in PeopleSoft page. May Place this code in FieldDefault event.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_TIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties.

For Date Time Field, use the following SQL.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_DATETIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties. Select Display Century and Display Time Zone in Page Field Properties to display complete date and time.

You will see this on page as follows:`

How to Default sysdate (Current Date) for Crystal Report and PeopleTools Query prompt automatically

2007-12-07T12:04:00.001-05:00

If you have a Query or Crystal Report, that has a date prompt and you want to schedule the report daily, so that date value is defaulted to sysdate (Current date), you can do this as follows.

Open the Query in PeopleTools Query tool.

Go to Criteria tab. Right Click on Expression2 Column and Select Expression or Expr-Expr if you are using between operator.

Type the following in Edit Expression.

Type	Default Date	Expression
Criteria	current date	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate),:1)
	current date - 1	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)-1,:1)
	current date + 1	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)+1,:1)

replace :1 with the actual prompt value. You must first create this prompt.

Now you need to pass 01/01/1900 as an input parameter if you want to run the query for current date. Passing any other values will make the query run for that date.

This way you can achieve both i.e. run the query for a user selected date or run a query for current date which can be used to schedule. Note : you can use any date as a replacement for sysdate and not just 01/01/1900.

Also you can default it to any day relative to current date for e.g. trunc(sysdate) - 1 or trunc(sysdate) + 1 etc.

If you want to know what parameter user has passed in your report, you can add the following expression as field in your query.

Create an expression of type Date in left hand side Under Expressions.

Type	Default Date	Expression
Field	Current Date	decode(:1,'1900-01-01',to_char(trunc(sysdate),'YYYY-MM-DD') ,:1)
	Current Date - 1	decode(:1,'1900-01-01',to_char((trunc(sysdate)-1),'YYYY-MM-DD') ,:1)
	Current Date + 1	decode(:1,'1900-01-01',to_char((trunc(sysdate)+1),'YYYY-MM-DD') ,:1)

I have tested this on Oracle 9.2.0.8 and PeopleTools 8.48.12.

ORA-00022: invalid session ID; access denied

2007-12-07T11:06:00.001-05:00

If you are getting this error, here is a quick fix.

Set the following value in psappsrv.cfg (appserver) and psprcs.cfg (batch server) for all the configured appservers and batch servers. You may have to reconfigure the appserver and batch servers and restart them.

DbFlags=8

Default value is DbFlags=0 which means use Persistent Secondary DB Connection.

Setting DbFlags=4 is not recommended by PeopleSoft, which completely disables the secondary database connection.

Setting it to 8 disables Persistent Secondary DB Connection, but it still uses on demand Secondary DB Connection for each request. This is required for using GetNextNumberWithGapsCommit (GNNWGC) function, which is internally used by PeopleSoft for workflow transactions to generate APPR_INSTANCE

If you do not do this, you may get row inserted in PS_APPR_INST_LOG with

APPR_INSTANCE = 0

which may cause, some undesired workflow routings.

The easiest solution I have found is

delete from ps_appr_inst_log where APPR_INSTANCE = 0

Please make sure that you backup the data and test it.

Here is some more information on GetNextNumberWithGapsCommit (GNNWGC) function from peoplebooks.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

Note. A secondary database connection is used to increment and retrieve record.field. The default behavior is to keep the secondary database connection persistent in order to improve performance for the next GetNextNumberWithGapsCommit usage. If the database administrator finds the persistent connection too high an overhead for the production environment (which should not be the case since PeopleSoft uses application server to mulitplex the database connection), the database administrator can change the default behavior to use an on-demand connection method. The persistent second connection is disabled using DbFlags bit eight in the application server and process scheduler configuration files. The second connection can be completely disabled using DbFlags bit four in the application server and process scheduler configuration files

This issue may be happening in Tools 8.45 and higher (8.46, 8.47, 8.48, 8.49 etc.), as secondary connection is introduced in 8.45.

See Peoplesoft Customer connection Resolutions for more information.

Resolution : 201049233 - E-ORACLE:10g Master Performance Solution for Oracle 10g

In this resolution, PeopleSoft generally recommends to Set DbFlags=8. I hope thay deliver this value by default in future peopletools releases.

Resolution : 201049902 - E-PC: Sporadic ORA-00022 Errors in AE w/ GetNextNumberWithGapsCommit PCode

Resolution : 201022463 - E-NV nVision reports that are run on Client Machine fail - Error message Invalid Cursor Number

Resolution : 201015931 - E-SEC: SQL looking for inactive roles causes slow logins for users

Resolution : 201023068 - E-WF:Deadlocks whenever a high load of transactions create worklists

Resolution : 201024183 - E-NV Running nVision via Define Report Book gives 8055 cursor entry error

Resolution : 200987809 - E-NV: nVision reports are queued when running in Windows 3 tier mode

Note : All the discussion is surrounding oracle database only. I found that it is disabled automatically for informix. For all other databases you may need to test the functionality your self.

Alternative to SendMail Function : PT_MCF_MAIL

2007-11-27T15:19:00.001-05:00

You can use Application package PT_MCF_MAIL (PeopleTools 8.46 or higher only) to generate emails instead of using SendMail peoplecode function.

Go to the PeopleBooks to get the complete description.

Home > PeopleBooks > Enterprise PeopleTools 8.48 PeopleBook: PeopleCode API Reference > Mail Classes

Go to Mail Classed Example section to see How to use these classes. Following examples are provided.

Creating Text Email
Creating Email and Overriding SMTP Settings
Creating HTML Email
Creating Multi-Part Email With Both Text and HTML Parts
Creating HTML Email with Images
Creating Email with Attachments
Creating Email Attachments Specifying a URL
Creating Multiple Emails
Authenticating Email While Sending

Advantages

Structured Code
Can generate HTML Email
Can override Default SMTP Parameters
Better Error Handling
Send emails return receipt request (email reception notification)
Ability to set Message priority

Reference

Rich emails using SendMail()

Issues

You might not be able to use GetHTMLText in Application Engine program run using process scheduler or in 2 tier mode, as this function does not support bind variables in 2 tier mode. PeopleBooks list the following constraints for this function.

Restrictions on Use

Use this function with the PeopleSoft Pure Internet Architecture. If run from a 2 tier environment, the parameter substitution does not take place.

Oracle Openworld 2007 PeopleSoft Conference Sessions

2007-11-23T13:40:00.001-05:00

You can download pdf file of the conference presentations for PeopleSoft at the following link.

PeopleSoft Sessions - Oracle Openworld 2007

Microsoft Oracle and PeopleSoft White papers

2007-11-21T12:20:00.001-05:00

This site provides the white papers specifically targeting Oracle PeopleSoft Enterprise Customers using Microsoft technologies for e.g. Share Point server, SQL Server. It also offers tips and techniques and provided a step by step instruction for integrating share point portal with PeopleSoft using web services.

Microsoft - Oracle - PeopleSoft white papers

Following are some of the interesting white papers that customers can take advantage of.

Connecting Microsoft Office InfoPath 2003 to PeopleSoft
Connecting PeopleSoft and Microsoft Excel using the Information Bridge Framework
Integrating Microsoft Office SharePoint Portal Server 2007 with PeopleSoft Enterprise Applications
Peoplesoft Performance Tuning on Microsoft SQL Server
PeopleSoft Red Paper: Microsoft SQL Server Tuning Tips for PeopleSoft 8.x
Connecting to PeopleSoft Web Services with Microsoft Visual Studio .NET
Connecting Microsoft Office InfoPath 2003 to Peoplesoft

You may also want to look at following Oracle Open World 2007 Session PDF file.

Integrating PeopleSoft Applications with Microsoft Oracle. (Steve Fox from Microsoft presented the session).

He demonstrated how to use Microsoft Visual studio 2008 Office programming to connect outlook with PeopleSoft back end data. Demo showed a form in outlook that displayed recruitment information from PeopleSoft in outlook.

Determine Languages installed for your PeopleSoft Application.

2007-06-01T10:46:00.001-04:00

You can use this SQL to find out which languages are already installed for your installation.

select * from PSLANGUAGES where installed = 1

Find if the current user has been assigned the named role

2007-05-10T16:19:00.001-04:00

You can use this in your peoplecode to determine if the current logged in user has role assigned to him.

&IND = %Roles.Find("MYROLENAME");

IF &IND <> 0 Then

/* User has this Role */

/* User does not have this Role */

end-if

You can also use the peoplecode function IsUserInRole("ROLENAME") to find the same.

Export to PDF Fix for Crystal Report 8.5

2007-04-20T14:00:00.001-04:00

If you try to run Export to PDF from Crystal Report 8.5 Report designer, it does not work. To resolve this, download this hot fix from businessobject web site and install it. Click here to get list of all downloads available for 8.5

Crystal Report Tip: Know the height and width of a object

2007-04-20T13:49:00.001-04:00

In Crystal Report, you can right click on any field or object and select Object Size and Position.

This can help you align two objects horizontally or vertically using same x and y coordinate and also allow you to set the same height and width. Very useful, when you need to delete one field and add another from different source. Just note down the all the values before deleting and set the values after adding the new object.

How to link Sub Report to Main Crystal Report

2007-04-20T13:41:00.001-04:00

Sub reports allows to use another query as a data source, different from the main query. You can have as many subreports as you want. Sub reports can not have another sub report inside it. One of the challenge is to link the sub report parameters with main report, so that sub report query is executed for the every row retrieved from the main report. This is easy.

I will explain this using a Sample. Let's say we are designing a report that prints user profile information. The main query is based on psoprdefn and is used in the main report. The Sub report is based on psroleuser and is used in the subreport to print role names for each user selected in the main query.

Click Insert -> Sub Report

Select Create a Sub Report and Type in PSROLEUSER and click on Report Expert.

Click on Database and Select the Query name. Complete the rest of steps by clicking on Next. Once you return to main Insert Subreport dialog box, Select Link tab. You can also change links for sub report later on by right clicking on sub report and selecting Change Subreport links.

Select Oprid from Available Fields and Click on > button to move it to Fields(s) to link To.

Now Click on Subreport Parameter fields to use:

Click on tiny Scroll bar, (Note: Do not use ?Pm-GF_C_PSOPRDEFN.OPRID as your parameter to link to).

Once you see the parameter field for the sub query i.e. user id select it.

Note: Both main query and subquery has a common parameter i.e. oprid or Userid. We need to match the Oprid field from main query and assign it to Parameter field of the sub query and do not use autogenerated Crystal Report parameter. There may be other ways to achieve the same results, but i found this works best.

Once you insert the subreport to main report, Right click on it and select border and colors. Set Line style to none for Left, Right, Top and Bottom. Right click on Subreport and Select Edit SubReport. You may want to Delete Report Header and Report footer in the subreport and arrange the fields and add any additional information you need. Also delete the Print Date special field.

Note: This is based on Crystal Report 8.5

Crystal Report 8.5 and SubReports

2007-04-20T09:48:00.001-04:00

If you have a linked sub report in Main Crystal Report, every time you open the main report, it re imports the sub report if it founds the sub report in the same location. This is despite the fact that Re-import Subreports when Opening Reports check box is unchecked.

This seems to me a bug. Similar bug in Crystal 10 has been reported on businessobject support web site.

Clearing the 'Re-import Subreport on Open' check box does not work (c2017466)

This creates a annoying behavior, as Crystal report will remove the links between main report and sub report. Only workaround I have found is to rename the sub reports or move them to other folders, once they are imported in main report. If Crystal Report does not find the sub report in the original location, it does not re import the sub report.

If any body has encountered the similar issues, please post it in the comments section.

Determine Hidden Folders and Content References for Portal Navigation

2007-03-26T16:01:00.001-04:00

If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.

To find all the folders which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE'
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and b.portal_Reftype = 'F' and a.portal_name = b.portal_name
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

To find all the content references which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE'
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and b.portal_Reftype = 'C' and a.portal_name = b.portal_name
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

Replace EMPLOYEE with Your portal name. Some of the Other names are.

CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in

PSPRSMSYSATTR

PSPRSMSYSATTRVL

Online Performance Configuration Guidelines for PeopleTools 8.45, 8.46 and 8.47, 8.48

2007-02-22T17:55:00.001-05:00

You should carefully review and implement ther performance guidelines to get Optimal performance from your PeopleSoft Application. This document is posted on Customer connection and you will need a loginid and password to access it. Here is the direct link.

Online Performance Configuration Guidelines for PeopleTools 8.45, 8.46 and 8.47, 8.48

Troubleshoot Workflow EMAIL (SMTP Server) issues.

2007-02-20T16:34:00.001-05:00

Many time End users get an Error message : Unable to Send Email, Invalid ID, while saving the page. This usually means some kind of issue while sending email from your appserver or batchserver using the SMTP Server. The error message is not correct and to get the exact reason, why send email is failing, you need to add/Update the following in your appserver configuration (PSAPPSERV.CFG) or batch server Configuration (PSPRCS.CFG) under SMTP Settings.

SMTPTrace=1

1 means enabled, 0 - Disabled. This setting is sometimes not present in PSPRCS.CFG. You have to add it manually opening the file. This setting is dynamic and does not require reboot of the Server, which is very good for troubleshooting in production environments.

Once turned on, it will generate Trace file SMTP.LOG in LOGS Folder under $PS_HOME/tools/appserv/DOMAIN for Appserver and $PS_HOME/tools/appserv/prcs/DOMAIN for Batchserver.

Open SMTP.LOG and fix the issues logged in it. This will make your users to save the page.

Login to Two Tier using your Enterprise LDAP ID.

2007-02-20T16:21:00.001-05:00

Normally, If you use LDAP Authentication for logging into your PeopleSoft application, you can not use the same id to login in Two Tier. This will force you to have multiple ids as you can do following in only two tier mode.

Data Mover access
Run Compare Reports
Build SQL Tables

This is not very convenient and not a secure approach. PeopleSoft SOLUTION ID 200735608 - E-LDAP: LDAP Authentication does not work in 2-tier describes this behavior.

I have found this undocumented Trick to use the same LDAP ID to login to App Designer in Two tier mode. I have tested this in PeopleTools 8.43 only and it may not work in higher tools release. (Let me know in comments) Note: This trick does not bypass peoplesoft security. If you do not have access to underlying components or menu items (for e.g. DATA MOVER Or Run compare reports or Build SQL), you won't be able to perform this.

Here are the Steps.

Login to App Designer in Three Tier. If you get an error message (You cannot sign on because the password for this user (PSOPRDEFN.OPERPSWD) isn't encrypted. Run encrypt_password * in Datamover, or change the password in Maintain Security.) , Press OK to continue and you will be logged in App Designer. The above message is a warning only and it does not prevent you from signon. This is identified as a known issue and is fixed in PeopleTools 8.46. See SOLUTION ID 200776124 - E-LDAP: Cannot logon 3 tier with LDAP user as ENCRYPTED field on PSOPRDEFN is set to 0
Now Login to App Designer by running this from Start -> Run or create a shortcut on the desktop.

<path to pside>pside.exe -CT ORACLE -CD dbname -CO userid -CI people -SUBSEQUENT -MN"APPLICATION_DESIGNER"

Replace ORACLE With your DBType, userid with your LDAP Userid, people with your connectid if different. Note you do not need password as you are already logged in.
You can run Data Mover from Go -> Data Mover Command once you are logged in 2 Tier mode.

Use Dynamic SQL for Prompts - SqlText

2007-02-20T16:02:00.001-05:00

Normally you can not use dynamically generated SQL at runtime as your prompt record. You can use %EDITTABLE to specify the prompt table you want to use, but it has to be predefined. There is this little known property SqlText for Field Class, which allows you to do exactly that.

Here is the description and sample code taken from peoplebooks.

This property is valid only for fields that have a dynamic view as their prompt record. If you set SqlText to a non-null value, that text is used instead of the dynamic view's normal text used for prompting.

Suppose you wanted to have a different prompt table depending on the settings of other fields in the row. Normally you could use %EDITTABLE to dynamically specify the prompt table you want. However in this case there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in PeopleCode based on what the user enters.

If you use a dynamic view as the prompt table, and have the dynamic view contain a SQL object that is updated from PeopleCode, you could achieve this functionality. However, a SQL object is a shared object, so if multiple users used the same page, they overwrite each other's settings and the SQL object contains the SQL for the most recent user. Similarly if a single user had multiple rows on a page, the SQL object is valid only for the most recent row. This means if the user went to another row and did a prompt, they would get the wrong values again.

The purpose of this property is to enable you to specify the generated SQL text independently for each occurrence in each transaction. It enables you to override the text of a dynamic view being used as a prompt table on a field by field basis.

It is up to the developer to verify that the text specified for this property is valid, that is, that it selects the correct number of fields for the record definition, and so on.

This property is read-write.

Local string &SQLSTRING;

Function set_jrnl_id_prompt();
&SQLSTRING = "SELECT DISTINCT JOURNAL_ID, BUSINESS_UNIT_IU, JOURNAL_DATE, LEDGER_GROUP, SOURCE, SYSTEM_SOURCE, PROC_PART_ID, JRNL_HDR_STATUS, DESCR FROM PS_JRNL_HEADER WHERE JRNL_HDR_STATUS IN ('N','E','V')"
If All(JRNL_EDIT_REQ.BUSINESS_UNIT) Then
&SQLSTRING = &SQLSTRING | " AND BUSINESS_UNIT_IU='" | JRNL_EDIT_REQ.BUSINESS_UNIT | "'"
End-If;
If All(JRNL_EDIT_REQ.LEDGER_GROUP) Then
&SQLSTRING = &SQLSTRING | " AND LEDGER_GROUP='" | JRNL_EDIT_REQ.LEDGER_GROUP | "'"
End-If;
If All(JRNL_EDIT_REQ.SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SOURCE='" | JRNL_EDIT_REQ.SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.SYSTEM_SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SYSTEM_SOURCE='" | JRNL_EDIT_REQ.SYSTEM_SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.PROC_PART_ID) Then
&SQLSTRING = &SQLSTRING | " AND PROC_PART_ID='" | JRNL_EDIT_REQ.PROC_PART_ID | "'"
End-If;
GetRecord().GetField(Field.JOURNAL_ID_FROM).SqlText = &SQLSTRING;
GetRecord().GetField(Field.JOURNAL_ID_TO).SqlText = &SQLSTRING;
End-Function;

Data being added conflicts with existing data

2006-12-19T16:33:00.001-05:00

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);
&LENGTH = INSTALLATION.EMPLID_LENGTH;
&CHECK = Rept("9", &LENGTH);
SQLExec("Update PS_INSTALLATION Set EMPLID_LAST_EMPL = EMPLID_LAST_EMPL + 1");
SQLExec("Select EMPLID_LAST_EMPL From PS_INSTALLATION", &EMPLID);
If Value(&EMPLID) > Value(&CHECK) Then
SQLExec("Update PS_INSTALLATION Set EMPLID_LAST_EMPL = EMPLID_LAST_EMPL - 1");
Error MsgGet(1000, 74, "The maximum Employee ID of %1 has been assigned.", &CHECK);
Else
&EMPLID = Rept("0", &LENGTH - Len(&EMPLID)) | &EMPLID;
End-If;
End-Function;

Here is the new PeopleCode

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

Search PeopleSoft Customer Connection for Solutions to most common issues

2006-12-15T10:35:00.001-05:00

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.

Enterprise Tools : Spreadsheet of All Tools Solutions
- Core Tools : Application Engine, Cobol, Workflow, PIA, Enterprise Portal
- App Development : Application Designer, LDAP, Security, Peoplecode, Panel Processor, Mobile
- Install and Upgrade : Change Assistant, Install, Upgrade, Certification
- Reporting Tools : Query, Tree Manager, Crystal, nVision, Report distribution, Ren Server, Process scheduler, ACE, Cube Manager, BAM
- Server Tools : Application Server, Web Server, Tuxedo, Verity
- Performance : Performance, Performance Monitor
- Integration Tools : Integration Broker, Component Interface, Business Interlink, Multi Channel Framework
- Updates and Fixes
- Documentation and Training Tools : User Productivity Kit, PeopleBooks, End User Training
Financial Management
Human Capital Management
Campus Solutions
Customer Relationship Management
Enterprise Learning Management
Enterprise Performance Management

Download Oracle OpenWorld Presentations for Peoplesoft

2006-12-11T11:58:00.001-05:00

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.

Search Customer Connection Effectively

2006-10-19T14:53:00.000-04:00

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

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

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

2006-10-06T10:38:00.000-04:00

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

http://java.sun.com/developer/technicalArticles/In...

http://www.hp.com/products1/unix/java/DST-US.html

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
Component: TIMEZONEPNLGRP
Page: TIMEZONEDATA, DSTDATA
Record: PSTIMEZONE, PSDSTTIME

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' )
UPDATE PSTIMEZONE SET DSTSTART='22ndSunMar' WHERE TIMEZONE IN ('AKST','CST','EST','MST','PST');
UPDATE PSTIMEZONE SET DSTEND='2FirstSunNov' WHERE TIMEZONE IN ('AKST','CST','EST','MST','PST');

Define Optional Query Prompt/Criteria in PeopleSoft Query

2006-10-03T11:11:00.000-04:00

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.

Execute query from Lefthand Portal Navigation

2006-09-26T16:03:00.000-04:00

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

Portal URL : q/?ICAction=ICQryNameURL=PUBLIC.YOUR_QUERY_NAME

Content Reference Attributes

Name: NAVNEWWIN

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.

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

2006-09-20T10:16:00.000-04:00

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.

SELECT * FROM PSAUTHITEM WHERE CLASSID IN
(SELECT OPRCLASS FROM PSOPRCLS WHERE OPRID='xxxxx') AND
MENUNAME='APPLICATION_DESIGNER' AND BARNAME='UPGRADE'

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.

Reset your PeopleSoft Password or AccessID Password

2006-08-18T10:44:00.000-04:00

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;

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

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

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

2006-08-08T14:56:00.000-04:00

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.

Crystal Report - Sub Report Unhandled Exception

2006-07-13T16:00:00.001-04:00

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.

Portal registry search index No Success

2006-06-21T11:03:00.000-04:00

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 10.25.55.000000PSAESRV.28984 [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.

Create Portal Registry Content Reference for a Query

2006-06-20T18:01:00.000-04:00

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

q/?ICAction=ICQryNameURL=PUBLIC.MYQUERYNAME

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
Portal URL : q/?ICAction=ICQryNameURL=PUBLIC.MYQUERYNAME
Note : No forward slash before q. Otherwise Portal template will not be removed.
Content Reference Attributes
Name : NAVNEWWIN
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.

NewLine in Workflow Emails

2006-05-31T10:39:00.000-04:00

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

Downloadble PeopleSoft Software and Documentation

2006-05-30T17:59:00.000-04:00

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

Directory Interface Troubleshooting Tips

2006-05-04T18:01:00.000-04:00

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
action (DSAUDITACTN) = AD

Can't connect to the LDAP server

Invalid LDAP Directory Server Address
Invalid Directory Server Port
If using SSL, Expired SSL Certificate or invalid SSL Port
No cert7.db locaed inside the appserver domain for e.g. $PS_HOME/appserv/domainname/cert7.db
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.

Run PeopleSoft Query from Excel or to Excel

2006-04-04T17:22:00.000-04:00

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.

http://hostname:port/psc/ps/EMPLOYEE/EMPL/s/>
WEBLIB_XXX.QRYRUN.FieldFormula.IScript_ToExcel?
userid=yyy&pwd=zzz&disconnect=y&type=public&
qryname=PT_SEC_PTLS_USERS&
MENUNAME=DATA_MOVER

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

Developer's Short cut to launch AE programs as processes

2006-02-24T10:03:00.000-05:00

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

-CT %%DBTYPE%% -CD %%DBNAME%% -CO %%OPRID%% -CP %%OPRPSWD%% -R %%RUNCNTLID%% -I %%INSTANCE%% -AI %%RUNCNTLID%% -OT %%OUTDESTTYPE%% -FP %%OUTDEST%% -OF %%OUTDESTFORMAT%%

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
User ID TEST
Run Control AEPROGNAME
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.

Initialize Application Engine State Records

2006-02-23T10:12:00.000-05:00

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
&rec.GetField(&I).SetDefault();
End-If;
End-For;
End-Function;

/* 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);
SetDefaultValues(&staterec);
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

2006-02-23T09:59:00.000-05:00

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
select * from PSAPMSGPUBHDR WHERE PUBSTATUS IN (0,6);
Publication Contract Script
select * from PSAPMSGPUBCON WHERE PUBCONSTATUS IN (0,6);
Subscription Contract Script
select * from PSAPMSGSUBCON WHERE SUBCONSTATUS IN (0,6);

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

2006-02-20T12:25:00.000-05:00

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

PeopleSoft Tutorial - nVision/Java/Chart

2006-02-14T16:38:00.000-05:00

You can download these free tutorials provided by Integration Consulting Inc.

Project Fusion - Transform SQR to Java skills

Use Peoplecode to create awesome charts

Create a Query and create an nVision Report

Disclaimer: I am not affiliated with Integration Consulting inc.

Trace Unique PeopleSoft Session

2006-02-10T09:40:00.000-05:00

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
http://yourwebserver.com/psp/ps/?cmd=login&trace=y

Monitor PeopleSoft Application Server Performance

2006-01-20T15:39:00.000-05:00

use the script jsmon.sh to monitor your Appserver processes

Meta HTML Variables

2006-01-19T16:35:00.000-05:00

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.

How To Skip Search Dialog Box

2006-01-04T19:06:00.001-05:00

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.

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.

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

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

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/
NodeName/content_type/content_id?content_parm

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

https://servername/psp/ps/EMPLOYEE/EMPL/c/
UTILITIES.URL_TABLE.GBL?Page=URL_TABLE&Action=U&URL_ID=CMDOCDB

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 ID: URL_ID

Key Value: CMDOCDB

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

Send SQR Generated Files to Report Repository

2005-12-30T10:14:00.000-05:00

When we use SQR file open to generate a file, it normally get's
generated in FILEPREFIX directory which is set by setfile.sqc included
in setenv.sqc which is normally defaulted to /usr/tmp. This file is not
transferred to report repository and user needs to manually ftp the
file from unix server. There is a way by which we can send the
generated file to report repository.

Call this procedure in your
sqr. This initializes the $directory variable which can be then be used
in place of '{FILEPREFIX}' to set the filepath. This generates the file
in the sqr log directory which is then transferred to report repository.

BEGIN-PROCEDURE GET-FILE-PATH
!Taken Code from fin2025.sqr
do Get-Directory
let $directory = rtrim($directory, ' ')
! database platform

#ifdef MVS
let $direct_temp = translate($directory, '','.')
let $directory = $direct_temp
let #drive_length = instr($directory, ':', 1)
let #path_length = length($directory)
add 1 to #drive_length
let $directory = {PS-SUBSTR}($directory, #drive_length, #path_length)
let $directory = 'DSN:' || $directory
#else
#ifdef OS400
let $direct_temp = translate($directory, '','.')
let $directory = $direct_temp
let #drive_length = instr($directory, ':', 1)
let #path_length = length($directory)
add 1 to #drive_length
let $directory = {PS-SUBSTR}($directory, #drive_length, #path_length)
let $directory = 'DSN:' || $directory
#else
#ifdef UNIX !UPD110341
let $direct_temp = translate($directory, '','/')
let $directory = $direct_temp
#else
!continue
#end-if
#end-if
#end-if

DISPLAY 'Destination Directory : ' NOLINE
DISPLAY $DIRECTORY
PRINT 'Destination Directory : ' (+1,1,)
PRINT $DIRECTORY ()

END-PROCEDURE

BEGIN-PROCEDURE Get-Directory

begin-select
ORIGOUTDEST,
PRCSOUTPUTDIR
! if isblank(rtrim (upper( ' '))
if rtrim (upper( ' ') = '%%LOG/OUTPUT DIRECTORY%%'
let $directory = rtrim( ' ') || ''
else
let $directory = rtrim( ' ')
end-if
from PSPRCSPARMS where
PRCSINSTANCE = #prcs_process_instance
end-select

END-PROCEDURE

Refresh webserver Cache without restarting it

2005-12-23T14:22:00.001-05:00

You can set auditPWD parameter in configuration.properties (pre 8.44) or web profile (> 8.44) to refresh the webserver cache dynamically without restarting it. This is really helpfull when you do not want to disturb your developer population.

auditPWD=dayoff

Set this to something unique for e.g. dayoff and fireoff this url to refresh the cache
http://myserver:port/psp/ps/?cmd=purge&pwd=dayoff

To view configuration.properties

http://myserver:port/psp/ps/?cmd=viewconfig&pwd=dayoff
http://myserver:port/psp/ps/?cmd=viewsprop

To Reset Timeout

http://xxx.xxx.com:5500/psp/ps/?cmd=resettimeout&pwd=xxxx

Debug PeopleCode using GetFile

2005-12-22T14:34:00.000-05:00

To debug a peoplecode (See complete document), we can use File write very effectively.

Step 1

Declare a file variable
Local File &MYFILE;

Step 2
Add the following Code. replace ketan with your username to uniquely identify your name.

&MYFILE = GetFile("debugketan.txt", "W");
If &MYFILE.IsOpen Then
&MYFILE.WriteLine("your text");
&MYFILE.WriteLine("ketan");
&MYFILE.Close();
End-If;

Step 3:

This file will be created in unix under respective appserver

PS_HOME\APPSERV\\files

For e.g. if peoplecode is running on hrdev appserver the file will be created on

/disk01/psoft/hr84x/appserv/hrdev/files directory.

PeopleSoft Record Types

2005-12-22T14:20:00.000-05:00

0 - table
1 - View
2 - Derived/Work
3 - Subrecord
5 - Dynamic View
6 - Query View
7 - Temp table

Extract PeopleCode

2005-12-22T14:15:00.000-05:00

If you want to create a file that contains all the PeopleCode for a project (or database) you can use the Find In. . .feature, and search for ";". Be sure to select Save PeopleCode to File. (Do not use double quotes).