Thursday 23 April 2015

Get the output files in zip format after running AE or SQR

Get the output files in zip format after running AE or SQR

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

Global integer Π
 &PI = PSQUERY_AET.PROCESS_INSTANCE;
&query = PSQUERY_AET.QRYNAME;

 SQLExec("SELECT B.URL,A.PRCSOUTPUTDIR from PS_CDM_DIST_NODE B, PS_CDM_LIST A WHERE A.DISTNODENAME = B.DISTNODENAME AND  A.PRCSINSTANCE = :1", &PI, &url, &output);

  REM  The file I want to compress;

&fileNameToZip = &output | "/" | &query | "-" | &PI | ".xls";

&fileout = &output | "/" | &query | "-" | &PI | ".zip";

&filename = &query | "-" | &PI | ".xls";

 REM  The internal zip file's structure -- internal location of file;

&zipInternalPath = &filename;

 Local JavaObject &zip = CreateJavaObject("java.util.zip.ZipOutputStream", CreateJavaObject("java.io.FileOutputStream", &fileout, True));

 Local JavaObject &file = CreateJavaObject("java.io.File", &fileNameToZip);

REM  to read &fileNameToZip into a buffer and write it out to &zip;

Local JavaObject &buf = CreateJavaArray("byte[]", 1024);

 Local number &byteCount;

Local JavaObject &in = CreateJavaObject("java.io.FileInputStream", &fileNameToZip);

 Local JavaObject &zipEntry = CreateJavaObject("java.util.zip.ZipEntry", &zipInternalPath | "/" | &file.getName());

 &zipEntry.setTime(&file.lastModified());

 &zip.putNextEntry(&zipEntry);

 &byteCount = &in.read(&buf);

 While &byteCount > 0

   &zip.write(&buf, 0, &byteCount);

   &byteCount = &in.read(&buf);

End-While;


&in.close();

&zip.flush();

&zip.close();

 &file.delete();

 Local string &MAIL_CC, &MAIL_TO, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES;

Local number &MAIL_FLAGS;

 &MAIL_FLAGS = 0;

 /* Code new */

Local string &runcntrl_id;

&runcntrl_id = PSQUERY_AET.RUN_CNTL_ID.Value;

&oprid = PSQUERY_AET.OPRID.Value;

If &oprid = "XXXXX" Then

   SQLExec("SELECT EMAIL_LIST FROM PS_UC_RNCTRL_EMAIL where RUNCONTROLID = :1", &runcntrl_id, &MAIL_TO);

Else

   SQLExec("select emailid from PSOPRDEFN where OPRID =:1", &oprid, &MAIL_TO);

End-If;

/* Code new */

&MAIL_CC = "";

&MAIL_BCC = "";

&MAIL_TEXT = &query;

&MAIL_FILES = &fileout;

&MAIL_TITLES = &query | "-" | &PI | ".zip";

&MAIL_SUBJECT = &query;

 &RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES);

 If &RET <> 0 Then

   MessageBox(0, "", 0, 0, "Sending Email Failed, Email IDs are  " | &RET);

Else

   MessageBox(0, "", 0, 0, "Succesfuly sent Emails to following Email IDs " | &RET);

End-If;

Sunday 19 April 2015

Start a missing PSAPPSRV service



Start a missing PSAPPSRV

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

XXXX-XXXXX)-<PS_HOME>psadmin.sh

PSADMIN -- Tools Release: 8.18.15
Copyright (c) 1988-2002 PeopleSoft, Inc.  All Rights Reserved.

--------------------------------
PeopleSoft Server Administration
--------------------------------


  1) Application Server
  2) Process Scheduler
  3) Web Components
  q) Quit


Command to execute (1-3, q): 1


--------------------------------------------
PeopleSoft Application Server Administration
--------------------------------------------

  1) Administer a domain
  2) Create a domain
  3) Delete a domain
  q) Quit

Command to execute (1-3, q) : 1


Tuxedo domain list:

  1)  FS
  2)  FS_T1
  3)  FS_T2

Select domain number to administer: 2



--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: FS_T1

  1) Boot this domain
  2) Domain shutdown menu
  3) Domain status menu
  4) Configure this domain
  5) TUXEDO command line (tmadmin)
  6) Edit configuration/log files menu
  7) Messaging Server Administration menu
  q) Quit

Command to execute (1-7, q) : 5
Loading command line administration utility ...
tmadmin - Copyright (c) 1996 BEA Systems, Inc.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by BEA Systems, Inc.
TUXEDO is a registered trademark.

> psr
Prog Name      Queue Name  Grp Name      ID RqDone Load Done Current Service
---------      ----------  --------      -- ------ --------- ---------------
BBL            58678       XXXX         XXXX  XXXXX(  IDLE )
MISSING ID=1
PSAPPSRV       APPQ        APPSRV         2  89997   4499850 ICPanel
PSAPPSRV       APPQ        APPSRV         3  90392   4519600 ICPanel
PSAPPSRV       APPQ        APPSRV         4  91276   4563800 ICPanel
PSAPPSRV       APPQ        APPSRV         5  90998   4549900 ICPanel
PSAPPSRV       APPQ        APPSRV         6  90386   4519300 ICPanel
PSAPPSRV       APPQ        APPSRV         7  90540   4527000 ICPanel
PSAPPSRV       APPQ        APPSRV         8  91016   4550800 ICPanel
WSL            00001.00020 BASE          20      0         0 (  IDLE )
PSQRYSRV       QRYQ        APPSRV        70      0         0 (  IDLE )
PSQRYSRV       QRYQ        APPSRV        71      0         0 (  IDLE )
PSSAMSRV       SAMQ        APPSRV       100      0         0 (  IDLE )
JSL            00095.00200 JSLGRP       200      0         0 (  IDLE )
JREPSVR        00094.00250 JREPGRP      250     80      4000 (  IDLE )

SELECT OPTION 5
TUXEDO command line (tmadmin) and run below, as it was 1 that missing so I used 1, you can use whatever is missing.

> boot -i 1
INFO: TUXEDO(r) System Release 6.5
INFO: Serial #: 101998665, Expiration NONE, Maxusers 1000000
INFO: Licensed to: PeopleSoft

Booting server processes ...

exec PSAPPSRV -- -C psappsrv.cfg -D FS_T1 -S PSAPPSRV :
        process id=17525 ... Started.
1 process started.

> psr
Prog Name      Queue Name  Grp Name      ID RqDone Load Done Current Service
---------      ----------  --------      -- ------ --------- ---------------
BBL            58678       XXXXX       0 199520   9976000 (  IDLE )
PSAPPSRV       APPQ        APPSRV         1      4       200 ICPanel
PSAPPSRV       APPQ        APPSRV         2  90036   4501800 ICPanel
PSAPPSRV       APPQ        APPSRV         3  90427   4521350 ICPanel
PSAPPSRV       APPQ        APPSRV         4  91318   4565900 ICPanel
PSAPPSRV       APPQ        APPSRV         5  91037   4551850 ICPanel
PSAPPSRV       APPQ        APPSRV         6  90423   4521150 ICPanel
PSAPPSRV       APPQ        APPSRV         7  90579   4528950 ICPanel
PSAPPSRV       APPQ        APPSRV         8  91054   4552700 ICPanel
WSL            00001.00020 BASE          20      0         0 (  IDLE )
PSQRYSRV       QRYQ        APPSRV        70      0         0 (  IDLE )
PSQRYSRV       QRYQ        APPSRV        71      0         0 (  IDLE )
PSSAMSRV       SAMQ        APPSRV       100      0         0 (  IDLE )
JSL            00095.00200 JSLGRP       200      0         0 (  IDLE )
JREPSVR        00094.00250 JREPGRP      250     80      4000 (  IDLE )

> quit


--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: FS_T1

  1) Boot this domain
  2) Domain shutdown menu
  3) Domain status menu
  4) Configure this domain
  5) TUXEDO command line (tmadmin)
  6) Edit configuration/log files menu
  7) Messaging Server Administration menu
  q) Quit

Command to execute (1-7, q) :
----------------------------------------------------------

Saturday 18 April 2015

Reset the administration login password for IB gateway

Reset the administration login password for IB gateway

1.  Use Pscipher present in location:
<PIA_HOME>\webserv\<Domain>\bin or piabin
2.  Encrypt the password using pscipher.
PSCipher.sh password
3.  Copy the encrypted password and paste at below location:
PS_HOME\webserv\peoplesoft\applications\peoplesoft\PSIGW\WEB-INF in weblogic...there will be gatewayUserprofile.xml

<Password>Encrypted password here<Password>

4.  Restart gateway webserver.

Tuesday 14 April 2015

SQL Tuning with SQL Plan Management

SQL Tuning with SQL Plan Management

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

[oracle@localhost ~]$ sqlplus pmuser/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 12 09:48:13 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1@ORCL> create table t as select * from dba_objects;

Table created.

PDB1@ORCL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('PMUSER');

PL/SQL procedure successfully completed.

Step 1: Verify that OPTIMIZER_USE_SQL_BLAN_BASELINES is set to true (the default)


PDB1@ORCL> show parameter baselines




NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE


step 2: enable auto capture for this session, run a statement twice and disable auto capture again.


PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> variable var42 varchar2(42);
PDB1@ORCL> exec :var42 := 'PMUSER';

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.


Now we should have a baseline for this sql:

PDB1@ORCL> set linesize 300
PDB1@ORCL> column sql_handle format a20
PDB1@ORCL> column plan_name format a42
PDB1@ORCL> column sql_text format a42
PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO 


Notice how there is one baseline for this statement and it is automatically set to ACCEPTED. Now we create an index, re-run the query with auto capture enabled to collect a new baseline with the index scan.


PDB1@ORCL> create index t_idx on t (owner);

Index created.

PDB1@ORCL> exec dbms_stats.gather_schema_stats ('PMUSER');

PL/SQL procedure successfully completed.

PDB1@ORCL> alter system flush shared_pool;

System altered.

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

Check the plan that is used to execute the query and notice how the explain plan mentions the use of a baseline:
PDB1@ORCL> set pagesize 1000
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 364z0straymuv, child number 0
-------------------------------------
select count(*) from t where owner = :var42

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time         |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |      |      |       | 426 (100)  |              |
| 1 | SORT AGGREGATE    |      | 1    | 6     |            |              |
|* 2 | TABLE ACCESS FULL| T    | 5    | 30    | 426 (1)    | 00:00:01     |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"=:VAR42)

Note
-----
- SQL plan baseline SQL_PLAN_arrxanznkdmsa3fdbb376 used for this statement


23 rows selected.


Check the baselines table for the newly created but unaccepted baseline. Notice how the header mentions if the plan is accepted or not.


PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42 YES NO  NO


Have a look at both execution plans for this sql handle:


PDB1@ORCL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_abdfaaa7e926cf0a'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_abdfaaa7e926cf0a
SQL text: select count(*) from t where owner= :var42
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_arrxanznkdmsa3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |          | 1    | 6     | 426 (1)    | 00:00:01 |
| 1 | SORT AGGREGATE     |          | 1    | 6     |            |          |
|* 2 | TABLE ACCESS FULL | T        | 3143 | 18858 | 426 (1)    | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"=:VAR42)

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_arrxanznkdmsaded8ae2f Plan id: 3738742319
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation       | Name     | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 1    | 6     | 1 (0)      | 00:00:01  |
| 1 | SORT AGGREGATE   |          | 1    | 6     |            |           |
|* 2| INDEX RANGE SCAN | T_IDX    | 5    | 30    | 1 (0)      | 00:00:01  |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"=:VAR42)

47 rows selected.

Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no like this:

  set serveroutput on
  declare evolve_out CLOB;
  begin
  evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'NO' );
  dbms_output.put_line(evolve_out);
  end; 
/


This is what the report should look like:

GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

Task Information:
---------------------------------------------
Task Name : TASK_21
Task Owner : PMUSER

Execution Name : EXEC_131
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/03/2014 10:03:15
Finished : 07/03/2014 10:03:16
Last Updated :
07/03/2014 10:03:16
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------

SUMMARY
SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0

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

DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan
Name : SQL_PLAN_arrxanznkdmsaded8ae2f
Base Plan Name : SQL_PLAN_arrxanznkdmsa3fdbb376
SQL Handle : SQL_abdfaaa7e926cf0a
Parsing Schema : PMUSER

Test Plan Creator : PMUSER
SQL Text : select count(*) from t where owner= :var42

Bind Variables:
-----------------------------
1 - (VARCHAR2(128)): PMUSER


Execution Statistics:
-----------------------------
                     Base Plan                    Test Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    .00099                       .000002
CPU Time (s):        .000489                       0
Buffer Gets:         153                           0
Optimizer Cost:      426                           1
Disk Reads:          0                             0

Direct Writes:       0                             0
Rows Processed:      0                             0
Executions:          10                            10


FINDINGS
SECTION
---------------------------------------------------------------------------------------------

Findings (1):
-----------------------------
1. The plan was verified in 0.29000 seconds. It passed the benefit criterion
because its verified performance was 767.74440 times better than that of
the baseline plan.

Recommendation:
-----------------------------
Consider accepting the plan.


EXPLAIN PLANS
SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
Plan Id :         1
Plan Hash Value : 1071362934

----------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost | Time         |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT   |      | 1    | 6     | 426  | 00:00:01     |
| 1 | SORT AGGREGATE     |      | 1    | 6     |      |              |
| * 2| TABLE ACCESS FULL | T    | 5    | 30    | 426  | 00:00:01     |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("OWNER"=:VAR42)


Test Plan
-----------------------------
Plan Id : 2
Plan
Hash Value : 3738742319

----------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes  | Cost | Time       |
----------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1   |  6     | 1    | 00:00:01   |
| 1 | SORT AGGREGATE      |       | 1   |  6     |      |            |
| * 2 | INDEX RANGE SCAN  | T_IDX | 5   | 30     | 1    | 00:00:01   |
----------------------------------------------------------------------

Predicate Information (identified by operation
id):
------------------------------------------
* 2 - access("OWNER"=:VAR42)

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

PL/SQL procedure successfully completed.


After the review, run the evolve again but set commit to yes this time to actually evolve the new baseline.

set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'YES' ); 
dbms_output.put_line(evolve_out); 
end; 
/

Check the baseline table again and notice how both plans are now accepted.
PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                       SQL_TEXT                                    ENA ACC FIX
--------------------  ------------------------------  ------------------------------------------  --- --- ---
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42  YES YES NO
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42  YES YES NO


Let's verify that this new plan is used for queries from now on:
PDB1@ORCL> set autotrace on
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5


Execution Plan
----------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1    | 6     | 8 (0)      | 00:00:01  |
| 1  | SORT AGGREGATE     |       | 1    | 6     |            |           |
|* 2 | INDEX RANGE SCAN   |T_IDX  | 3143 | 18858 | 8 (0)      | 00:00:01  |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"=:VAR42)

Note
-----
- SQL plan baseline "SQL_PLAN_arrxanznkdmsaded8ae2f" used for this statement


The last step would be to think about what to do with the original baseline. Since both plans are accepted now, both are eligible for execution which could be helpful in situations where a adaptive cursor sharing kicks in and the full scan would actually be the better plan. In this case though I decided to delete the old baseline:

 declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => 'SQL_abdfaaa7e926cf0a',  
 plan_name => 'SQL_PLAN_arrxanznkdmsa3fdbb376'); 
 dbms_output.put_line(drop_result);    
 end; 
/


This showed how to create a SQL Plan Baseline for a query with auto capture. It demonstrated how even after adding an index, only the accepted baseline (with the full scan) was actually being used until we reviewed and evolved the new baseline.

 

Monday 13 April 2015

A Introduction to peopletool 8.54




1. Introduction
The recently launched version of PeopleTools 8.54 contains a broad range of enhancements. Although we’ve had the GA (General Availability) release we can’t upgrade existing environments until the 8.54.02 patch, so now is a good time to perform a fresh sandbox install to investigate the details and highlight the areas that are of most interest.
As in the first part of this series, there is a lot of content to cover so I’ve spread it over several entries. This first part looked at the back-end, infrastructure and System Admin changes, whereas this post moves up the software stack with integration and reporting, and the final entry will finish with the Fluid UI.
2. Developer UI Enhancements
There have been a number of improvements to the User Interface – we’re not talking about Fluid, this is in the Classic UI too although these changes may also benefit Fluid pages).
a. Charting Enhancements
The development team have introduced some new charting types to the toolset. We were already able to select from quite a few options, however we’ve now got Gauges, the LED lights, Status Meters and Funnel charts.
1-charts
b. Long Edit Box Character Counter
Previously people (myself included) have accomplished similar functionality – with varying levels of success – by inserting JavaScript onto the page. Now it’s natively built in to PeopleTools. A character counter can be activated on the properties on the Long Edit box control:
2-long edit setup
The result on-screen is like this:
3-long edit result
Note: this functionality doesn’t actually limit the text entry and if the user exceeds the limit it will show a negative number.
c. Pivot Grids
Pivot Grids have been enhanced in a lot of ways – there are almost 30 improvements listed in the Release Notes. Just picking a few, they now include the ability to restrict the number of rows shown in a Pivot Grid, PS Query drilling URLs and Bubble and Scatter charts are now available as Pivot Chart types.
4-pivots
3. Reporting
a. BI Publisher
As of PeopleTools 8.54, BI Publisher now includes support for PCL (Printer Control Language) code in RTF templates. This allows for printing PDF with Secure Fonts, essential for the secure printing of cheques.
Also newly supported are digital signatures – which can be used to verify the sender and to ensure that it the output hasn’t been amended in transit, and updatable PDFs.
b. PS Query
PS Query now supports defaults for prompts:
5-query
There have been workarounds to achieve a similar result before, but it’s now built into the configuration pages so we don’t need to search online for the workaround each time we want to use it.
Also new with PS Query is the option to include image fields in your output. There are a number of display alternatives with image fields, either Image Data (in which case PS Query will display the image inline with the rest of the HTML result set), or a Base64 encoded data string representing the image with any output other than HTML, or – when the property is set to Image Hyperlink – a URL to the image will be returned instead of the image itself. When the URL is clicked, the image will be displayed in a new browser window.
6-query images
Additionally, all PS Queries can be exposed as REST services, and Microsoft Excel 2007 and above is supported thereby increasing the amount of rows you can download from a Query result set into Excel above the previous limit of 64 thousand.
Finally, PeopleTools 8.54 introduces a new Query type, the Composite Query. Composite Queries are a superset of Connected Queries (which have been in PeopleTools for a couple of releases). Composite Queries allow users to connect queries together and have the output presented as a flat result set (instead of the hierarchical data sets which were output from Connected Queries).
4. Batch Processing
One very visible improvement to the Process Scheduler is a new status window that slides in from the lower right corner to give updates on processing progress. This is a nice touch that I’m sure end-users will appreciate:
7-batch window
Secondly, Activity Guides can improve batch processes as steps – which is important if there’s something a bit more process intensive that’s needed as part of a sequence of steps.
Finally, App Engine program trace allows you to specify which sections to appear in the trace, rather than having to wade through the trace for an entire program. This needs to be enabled in the Process Scheduler config, in Configuration Manager, and then in the App Engine itself:
8-trace
5. Other Enhancements
Other enhancements included in this version of PeopleTools are:
– There’s a WorkCenter to make the setting up of new Activity Guides easier, plus a cloning function which will be useful when similar – but slightly different – guides are needed.
SES facets now include numerical and date ranges. Results can include images and report repository content.
Change Assistant has now been decoupled from the PeopleSoft Image, enabling packages to be moved to subsequent environments without also needing to connect to the PeopleSoft Image (this was quite restricting in Tools 8.53). It also has a fresh new UI and can be scripting/configured via the command line
Data Migration Workbench has received improvements to Application Data Sets (and define relationships between groups), plus merging, support for managed objects and an improved UI.
PeopleSoft Test Framework now allows you to perform mass updates (updating a set of tests in one change), interaction with App Designer projects and some usability enhancements.
6. Conclusion
The next version of PeopleTools is bringing many improvements. Much is being made of the new User Interface – and rightly so – however there are other improvements that we will improve our workflow by making it both simpler and more efficient.

The purge process did not run because the Oprid configured to start the Process Scheduler did not have the required permissions to run the purge process.

  Purge process - Required permissions The PeopleSoft Oprid used to start the Process Scheduler, as defined in the Scheduler's psprcs.cf...