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