PeopleSoft Learning

View to analyze the Process in the process monitor in Peoplesoft.

 

This view gives clear information about the process with time taken for the execution in hours minutes and seconds.

 

SELECT PRCSINSTANCE

, PRCSNAME

, OPRID

, RUNCNTLID

, SERVERNAMERUN

, RQSTDTTM

, RUNDTTM

, BEGINDTTM

, ENDDTTM

, floor(TO_CHAR((enddttm-begindttm)*24*60)) AS MINUTES

, lpad(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-floor(TO_CHAR((enddttm-begindttm)*24*60)))*60)

,2

,0) AS SECONDS

, decode (FLOOR(TO_CHAR((enddttm-begindttm)*24*60))||':'||lpad(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-floor(TO_CHAR((enddttm-begindttm)*24*60)))*60)

,2

,0)

, ':'

,' '

,FLOOR(TO_CHAR((enddttm-begindttm)*24*60))||':'||lpad(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-floor(TO_CHAR((enddttm-begindttm)*24*60)))*60)

,2

,0)) AS MINSEC

, RUNSTATUSDESCR

FROM PS_PMN_PRCSLIST

ORDER BY RUNDTTM DESC

 

Posted by

Ganesh A.M

 

%InsertSelect

Syntax

%InsertSelect([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .)

Description

The %InsertSelect function will generate an INSERT statement with a SELECT for you. It does not generate the FROM statement. You must specify all the select records before you specify any over ride fields.

The INSERT column list is composed of all the fields in the specified insert_recname, with the exception of LONG_CHAR or IMAGE fields.

The following code:

%InsertSelect(AE_SECTION_TBL, AE_STEP_TBL S, AE_SECTION_TYPE = ' ')

   FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T

WHERE. . .

will resolve into the following:

INSERT INTO PS_AE_SECTION_TBL (AE_APPLID, AE_SECTION,..., AE_SECTION_TYPE)

SELECT S.AE_APPL_ID, S.AE_SECTION, ... ' '

FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T

   WHERE. . .

%InsertSelect(MY_TEMP, MY_TABLE1, MY_TABLE2 T2)

   FROM PS_MY_TABLE1 T1, PS_MY_TABLE2 T2

WHERE %Join(COMMON_KEYS, MY_TABLE1 T1, MY_TABLE2 T2) . . .

This code will resolve into:

INSERT INTO PS_MY_TEMP (FIELD1, FIELD2 . . .)

   SELECT T2.FIELD1, T2.FIELD2, . . .

FROM PS_MY_TABLE1 T1, PS_MYTABLE2 T2

WHERE T1.FIELD1 = T2.FIELD1

AND T1.FIELD2 = T2.FIELD2 . . .

-Posted by

Ganesh A.M

 

1. How to trace AE?

                

There are a few ways to trace an AE program. You can enable the trace using some of the below methods:

          

1. In the process scheduler configuration file psprcs.cfg
2. You can enable the trace in the “Process Definition”.
3. Grab the Oracle session information and generate SQL Trace

                

It is not advised to enable the trace using psprcs.cfg because it will enable trace for ALL AE programs using that scheduler. However, this might be the best choice in an environment where you want to trace multiple AE programs or you do not have access to modify the process definition. Exercise this option with caution knowing that it might generate trace files for all AE programs.

             

To enable trace using “Process Definition”, make the below change. You can use the “-TRACE” or “-TOOLSTRACESQL” or “-TOOLSTRACEPC” option depending on what information you require.

                                      

2. Which AE program has trace enabled in its process definition?

 

The below SQL will help you determine all the AE programs that have TRACE enabled. It will be useful to use this SQL in your monitoring scripts if you notice developers enabling trace using the process definitions and getting the program with its process definition migrated all the way to production or if your analyst or DBA have the tendency to enable the trace and forget about it.

            

SELECT PRCSNAME, PARMLIST

FROM PS_PRCSDEFN

WHERE UPPER(PARMLIST) LIKE '%TRACE%'

AND PRCSTYPE = 'Application Engine';

 

 

Posted by

Ganesh A.M

Peoplesoft Page Security View.

 

This view will give the clear mapping of the oprid and permission that he has on the set of the pages in particular menu path.

 

This view also displays ROLENAME and CLASSID through which oprid has the permission to a particular page.

 

SELECT DISTINCT h.oprid

, h.oprdefndesc

, g.rolename

, a.classid

,Decode(SUBSTR (b.menugroup

, 1

, INSTR (b.menugroup

, '&') - 1) || SUBSTR (b.menugroup

, INSTR (b.menugroup

, '&') + 1

, LENGTH (b.menugroup) )

,'PeopleSoft'

,'PeopleTools'

,SUBSTR (b.menugroup

, 1

, INSTR (b.menugroup

, '&') - 1) || SUBSTR (b.menugroup

, INSTR (b.menugroup

, '&') + 1

, LENGTH (b.menugroup) )) AS menugroup_label

, SUBSTR (b.menulabel

, 1

, INSTR (b.menulabel

, '&') - 1) || SUBSTR (b.menulabel

, INSTR (b.menulabel

, '&') + 1

, LENGTH (b.menulabel) ) AS menu_label

, a.menuname

, c.barname

, SUBSTR (c.barlabel

, 1

, INSTR (c.barlabel

, '&') - 1) || SUBSTR (c.barlabel

, INSTR (c.barlabel

, '&') + 1

, LENGTH (c.barlabel) ) AS bar_label

, SUBSTR (c.itemlabel

, 1

, INSTR (c.itemlabel

, '&') - 1) || SUBSTR (c.itemlabel

, INSTR (c.itemlabel

, '&') + 1

, LENGTH (c.itemlabel) ) AS menuitem_label

, c.pnlgrpname AS component

, DECODE (a.authorizedactions

, 1

, 'Add'

, 2

, 'Update/Display'

, 3

, 'Add Update/Display'

, 4

, 'Update/Display All'

, 5

, 'Add Update/Display All'

, 6

, 'Update/Display Update/Display All'

, 7

, 'Add Update/Display Update/Display All'

, 8

, 'Correction'

, 9

, 'Add Correction'

, 10

, 'Update/Display Correction'

, 11

, 'Add Update/Display Correction'

, 12

, 'Update/Display All Correction'

, 13

, 'Add Update/Display All Correction'

, 14

, 'Update/Display Update/Display All Correction'

, 15

, 'Add Update/Display Update/Display All Correction'

, 129

, 'Add Data Entry'

, 131

, 'Add Update/Display Data Entry'

, 135

, 'Add Update/Display Update/Display All Data Entry'

, 143

, 'Add Update/Display Update/Display All Correction Data Entry'

, 139

, 'Add Update/Display Correction Data Entry'

, 133

, 'Add Update/Display All Data Entry'

, 137

, 'Add Correction Data Entry'

, 141

, 'Add Update/Display All Correction Data Entry'

, 130

, 'Update/Display Data Entry'

, 134

, 'Update/Display Update/Display All Data Entry'

, 138

, 'Update/Display Correction Data Entry'

, 142

, 'Update/Display Update/Display All Correction, Data Entry'

, 132

, 'Update/Display All Data Entry'

, 140

, 'Update/Display All Correction Data Entry'

, 136

, 'Correction Data Entry'

, 128

, 'Data Entry' ) AS authorized_actions

, SUBSTR (d.itemlabel

, 1

, INSTR (d.itemlabel

, '&') - 1) || SUBSTR (d.itemlabel

, INSTR (d.itemlabel

, '&') + 1

, LENGTH (d.itemlabel) ) AS componentitem_label

, d.pnlname AS pagename

, DECODE (a.displayonly

, 1

, 'READONLY'

, 0

, 'WRITE') AS read_write

, c.itemnum

, d.subitemnum

FROM psauthitem a

, psmenudefn b

, psmenuitem c

, pspnlgroup d

, pspnlgrpdefn e

, psroleclass f

, psroleuser g

, psoprdefn h

WHERE a.menuname = b.menuname

AND a.menuname = c.menuname

AND a.barname = c.barname

AND a.baritemname = c.itemname

AND a.PNLITEMNAME=d.ITEMNAME

AND c.itemtype = 5

AND d.pnlgrpname = c.pnlgrpname

AND d.market = c.market

AND d.pnlgrpname = e.pnlgrpname

AND d.market = e.market

AND e.pnlgrpname = c.pnlgrpname

AND e.market = c.market

AND f.classid = a.classid

AND f.rolename = g.rolename

AND g.roleuser = h.oprid

AND h.acctlock = 0

 

-Posted by

Ganesh A.M

 

Creating Dynamic Roles in Peoplesoft

 

Using the Dynamic Members page of the role component, that users can be tied to roles using a query rule there on the page. The query itself should simply be built to select right there on the page. The query itself should simply be built to select the user IDs that should belong to the Role based on some logic in the query.

 

Problem : - Peoplesoft has built in security within the PS Query tool that will automatically add ‘AND OPRID =’ and the current user’s OPRID to all queries on tables that have PSOPRDEFN table in order to select OPRID’s will always only return your own OPRID.

 

Solution: - The solution to this problem is to create a view within Peoplesoft that will contain the query logic needed to return your data, or least a broad scope of data an criteria by which you may wish to choose your Role users. Be certain this view contains OPRID but doesn’t use it as a KEY. Once this is done then your PS Query selects OPRID from the view, not one of the delivered PS tables.

 

Tie the query to your Role

 

PeopleTools -> Security -> Permission & Roles in 8.4

 

PeopleTools -> Maintain Security -> Use -> Roles in earlier releases.

 

 

Select the server to run the query on in the Execute on Server field and then press Test Rule button. If the data returned looks correct the Click Execute  Rule button to actually run the process that will put the users in to the PSROLEUSER table.

 

 

-Posted by

Ganesh A.M

 

Useful Security Queries in Peoplesoft.

1) Users with a Particular Role

Select ROLEUSER, ROLENAME

FROM PSROLEUSER

WHERE ROLENAME = ‘ROLE1’

ORDER BY ROLEUSER

2) Users with a particular Permission list

Select OPRID, OPRCLASS

FROM PSOPRCLS

WHERE OPRCLASS = ‘PERM1’

ORDER BY OPRID

3) Users with a particular Primary permission list

Select ORPID, OPRCLASS

From PSOPRDEFN

WHERE OPRCLASS = ‘PERM1’

ORDER BY OPRID

4) Users with a Particular Row security Permission List

SELECT OPRID, ROWSECCLASS

FROM PSOPRDEFN

WHERE ROWSECCLASS = ‘PERM1’

ORDER BY OPRID

5) Users, Role & Class with Access to a particular page

Select B.ROLEUSER,C.OPRDEFNDESC, A.ROLENAME, A.CLASSID

FROM PSROLECLASS = A.ROLENAME

AND B.ROLEUSER = C.OPRID

AND C.ACCTLOCK = 0

AND A.CLASSID IN (SELECT DISTINCT CLASSID

FROM PSAUTHITEM

WHERE PNLITEMNAME = ‘Page1’

AND DISPLAYONLY = 0)

ORDER BY B.ROLEUSER

6) Roles & Class with Access to a particular page

SELECT A.ROLENAME, A.CLASSID

FROM PSROLECLASS A, PSROLEDEFN B

WHERE A.CLASSID IN

(SELECT DISTINCT CLASSID

FROM PSAUTHITEM

WHERE PNLITEMNAME = ‘Page1’

AND A.ROLENAME = B.ROLENAME

ORDER BY A.ROLENAME

7) Permission Lists & Details with Access to a Particular page

SELECT * From PSAUTHITEM

WHERE PNLITEMNAME = ‘Page1’

8) Roles Assigned to a particular User

SELECT ROLEUSER, ROLENAME

FROM PSROLEUSER

WHERE ROLEUSER = ‘USER1’

ORDER BY ROLENAME

9) Permission Lists assigned to particular User

SELECT ROLEUSER, ROLENAME

FROM PSROLEUSER

WHERE ROLEUSER = ‘USER1’

ORDER BY ROLENAME

10) Permission lists Assigned to a particular user

SELECT OPRID, OPRCLASS

FROM PSOPRCLS

WHERE OPRID = ‘USER1’

ORDER BY OPRCLASS

11) Roles & Permission lists Assigned to a Particular User

SELECT A.ROLEUSER, A.ROLENAME, B.CLASSID

FROM PSROLEUSER A, PSROLECLASS B

WHERE A.ROLEUSER = ‘USER1’

AND A.ROLENAME = B.ROLENAME

12) Permission lists assigned to a particular Role

SELECT * FROM PSROLECLASS

WHERE ROLENAME = ‘ROLE1’

ORDER BY CLASSID

13) All Pages Accessible by a user

SELECT A.ROLEUSER, A.ROLENAME , B.CLASSID

C.MENUNAME, C.BARNAME, C.BARITEMNAME,

C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS

FROM PSROLEUSER A, PSROLECLASS B, PSAUTHITEM C

WHERE A.ROLEUSER = ‘USER1’

AND B.CLASSID = C.CLASSID

ORDER BY A.ROLENAME, B. CLASSID,

C.MENUNAME, C.BARITEMNAME, C.PNLITEMNAME

14) All Pages Accessible by a Role

SELECT DISTINCT A.ROLENAME, B.CLASSID,

C.MENUNAME, C.BARNAME, C.BARITEMNAME,

C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS

FROM PSROLEUSER A, PSROLECLASS B, PSAUTHITEM C

WHERE A.ROLEANME = ‘ROLE1’

AND B.CLASSID = C.CLASSID

ORDER BY B.CLASSID,

C.MENUNAME, C.BARITEMNAME, C.PNLITEMNAME

15) All Pages Accessible by a Permission list

SELECT * FROM PSAUHITEM

WHERE CLASSID = ‘PERM1’

ORDER BY MENUITEM, BARITEMNAME, PNLITEMNAME

16) All users with Access to Maintains Security. (Excluding change password and user self service).

SELECT B.ROLEUSER, C.OPERDEFNDESC, A.ROLENAME, A.CLASSID

FROM PSROLECLASS A, PSROLEUSER B, PSOPRDEFN C

WHERE B.ROLENAME = A.ROLENAME

AND B.ROLEUSER = C.OPRID

AND B.ACCTLOCK = 0

AND A.CLASSID IN

(SELET DISTINCT CLASSID

FROM PSAUITEM

WHERE MENUITEM LIKE ‘MAINTAIN_SECURITY%’ AND PNLITEMNAME NOT IN

(‘CHANGE_PASSWORD’, ‘USER_SELF_SERVICE’)

AND DISPLAYONLY = 0)

ORDER BY ROLENAME, ROLEUSER

17) All Users with Access to Application Designer

SELECT DISTINCT A.OPRID, B.OPRDEFNDESC

FROM PSOPRCLS A, PSORDEFN B

WHERE A.OPRID = B.OPRID

AND B.ACCTLOCK = 0

AND A.OPRCLASS IN

(SELECT DISTINCT CLASSID

FROM PSAUTHIEM

WHERE MENUNAME LIKE ‘APPLICATION_DESIGNER%’

AND DISPLAYONLY = 0)

Posted by

Ganesh

Crontab commands

To View the jobs scheduled in Cron:

crontab –l

To edit the Crontab:

crontab –e

To remove the crontab:

crontab –r

To enable the new crontab file:

1. Create a file with all the jobs.

Let file name be: Jobstxt.lis

2. Enable new crontab

crontab Jobstxt.lis

Crontab Entries:

A crontab file consists of lines of six fields each. The fields are separated by spaces or tabs.The first five are integer patterns that specify the following:

minute (0-59) or * (all)

hour (0-23) or * (all)

day of the month (1-31) or * (all)

month of the year (1-12) or * (all)

day of the week (0-6 with 0=Sunday) or * (all)

Ex:

15 00 * * 1-5 ksh /prod830/bat/endofday.sh >/prod830/data/log/endofday.log 2>/prod830/data/log/endofday.err

The above will run at 00:15 every month,every year, from Monday to Friday. The filename is endofday.sh and writes log to endofday.log, if errorsout it writes into error file endofday.err

Example:

min	hour	day/month	month	day/week	Execution time
30	0	1	1,6,12	*	-- 00:30 Hrs on 1st of Jan, June & Dec.
0	20	*	10	1-5	--8.00 PM every weekday (Mon-Fri) only in Oct.
0	0	1,10,15	*	*	-- midnight on 1st ,10th & 15th of month
5,10	0	10	*	1	-- At 12.05,12.10 every Monday & on 10th of every month

Check this out:

0 0 1,15 * 1 ksh /prod830/bat/endofday.sh >/prod830/data/log/endofday.log 2>/prod830/data/log/endofday.err

Please note the above cron entry. The behaviour of above one:

would run command on the first and fifteenth days of each month, as well as every Monday.

Controls on Using the crontab Command

The /var/adm/cron/cron.allow and /var/adm/cron/cron.deny files control which users can use the crontab command. A root user can create, edit, or delete these files.

The following is an example of an cron.allow file:

root

nick

dee

sarah

If the cron.allow file exists, only users whose login names appear in it can use the crontab command. The root user's log name must appear in the cron.allow file

if the file exists. A system administrator can explicitly stop a user from using the crontab command by listing the user's login name in the cron.deny file. If

only the cron.deny file exists, any user whose name does not appear in the file can use the crontab command.

A user cannot use the crontab command if one of the following is true:

* The cron.allow file and the cron.deny file do not exist (allows root user only).

* The cron.allow file exists but the user's login name is not listed in it.

* The cron.deny file exists and the user's login name is listed in it.

Thanks and Regards

D.Sasi Kumar