Thursday, October 16, 2008

Identify List of users who has minimized the Menu Pagelet

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.

 

menu_minimize

Monday, July 21, 2008

Configuration Settings for Tracing

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.

Thursday, July 03, 2008

Synchronous App Message Error Status SQL

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

Monday, June 30, 2008

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

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

Friday, April 11, 2008

Schedule Query Security Access

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'

Thursday, April 03, 2008

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

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:`

image