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'

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

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

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

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

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

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

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.