Roles, Permission Lists & Components

The following SQL identifies which roles/permission lists a user has to access a given component.

select RUSR.OPRID,
       RUSR.ROLENAME,
       RCL.CLASSID,
       MNU.MENUNAME,
       CMP.PNLGRPNAME
  from PSROLEUSER_VW RUSR,
       PSROLECLASS RCL,
       PS_PSACLMENU_VW2 MNU,
       PS_ACLCOMPONENT_V2 CMP
 where RUSR.ROLENAME  = RCL.ROLENAME
   and RUSR.OPRID     = 'oprid'
   and RCL.CLASSID    = MNU.CLASSID
   and MNU.MENUNAME   = CMP.MENUNAME
   and CMP.PNLGRPNAME = 'component'
 order by MNU.CLASSID

SQL to find out which roles have a particular permission list.

select distinct RCL.ROLENAME,
                RCL.CLASSID
           from PSROLEUSER_VW  RUSR,
                PSROLECLASS  RCL
          where RCL.CLASSID = 'TYALLPLS'
       order by RCL.ROLENAME

SQL to list users assigned to a particualr role.

select *
  from PSROLEUSER_VW RUSR
 where RUSR.ROLENAME = 'Rolename'

Current Job Row

I know I can enter the job effective dating code in my sleep, but the following, with a few amendments as needed, should save some time in the future.

select JOB.EMPLID, JOB.EMPL_RCD, JOB.EFFDT, JOB.EFFSEQ, JOB.ACTION, JOB.ACTION_REASON, JOB.EMPL_STATUS, JOB.BUSINESS_UNIT, JOB.COMPANY, JOB.PAYGROUP from PS_JOB JOB where JOB.EFFDT = (select max(JOB1.EFFDT) from PS_JOB JOB1 where JOB.EMPLID = JOB1.EMPLID and JOB.EMPL_RCD = JOB1.EMPL_RCD and JOB1.EFFDT <= SYSDATE) and JOB.EFFSEQ = (select max(JOB2.EFFSEQ) from PS_JOB JOB2 where JOB.EMPLID = JOB2.EMPLID and JOB.EMPL_RCD = JOB2.EMPL_RCD and JOB.EFFDT = JOB2.EFFDT)

Note: The above example is for Oracle, if using SQL Server change SYSDATE to GetDate().

Query Security Record SQL

The following SQL identifes which PeopleSoft records use a particular Query Security Record.

select RECNAME
from PSRECDEFN
where QRYSECRECNAME = 'query security record'

SQL to find a Field in PeopleSoft

This script lists all the tables that contain a given field in PeopleSoft. Just replace fieldname with the name of the field you want to find.

SELECT A.RECNAME,
B.SQLTABLENAME
FROM PSRECFIELD A,
PSRECDEFN B
WHERE A.FIELDNAME = 'fieldname'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND B.RECNAME NOT LIKE 'AUDIT%'
AND B.RECNAME NOT LIKE '%TEO%'
AND B.RECNAME NOT LIKE '%AET'
AND B.RECNAME NOT LIKE '%TAO%'
AND B.RECNAME NOT LIKE '%PERS%'
AND B.RECNAME <> 'PSOPRALIAS'

How to Automatically Select Jobs on the Run Panel in PeopleSoft

To automatically select a job for running on the run panel in PeopleSoft add some PeopleCode to the RowInit event PRCSRQSTDLG_WRK.SELECT_FLAG to set this field.

e.g.

Evaluate %Component
When "COMPONENT_NAME_1"
SELECT_FLAG = "Y"
When "COMPONENT_NAME_2"
SELECT_FLAG = "N"
End-Evaluate;

Things You Do In Your Lunch Break - How to get the Ratio between two numbers in Excel

A while ago I was asked how to get the ratio between two numbers in Excel. One lunchbreak and two VBA functions later, this is what I came up with.

I ended up creating two functions. One returns the highest common denominator for two numbers HighDenom and the second, Ratio, returns a string of the ratio between two numbers (e.g. 5:1).

First off you need to add a couple of functions to your spreadsheet...

1. Open the workbook you're working on

2. Go into VBA (Alt+F11)

3. Insert a new module (Insert > Module)

4. Paste the following 2 functions into your module: -

Public Function HighDenom(intNum1 As Integer, intNum2 AsInteger) As Integer

' This procedure returns the Highest common denominator fortwo numbers.

If intNum1 > intNum2 Then
intMax = intNum1
Else intMax = intNum2
End If

For i = intMax To 1 Step -1
If (intNum1 / i = Int(intNum1 / i) And (intNum2 / i =Int(intNum2 / i))) Then
HighDenom = i
Exit Function
End If
Next

End Function

Public Function Ratio(intNum1 As Integer, intNum2 AsInteger) As String

' This procedure returns the Highest common denominator fortwo numbers.

intHD = HighDenom(intNum1, intNum2)
intDiv1 = intNum1 / intHDintDiv2 = intNum2 / intHD
Ratio = intDiv1 & ":" & intDiv2
End Function

5. Get out of VBA (Press Alt+Q)

6. Use the functions (They will appear in the Paste Function dialogbox (Shift+F3) under the "User Defined" category.

How to use %RECNAME_EDIT as a Prompt Table

Sometimes in PeopleSoft you want to be able to vary the contents of a drop down list. For example you may want to only give the user the choice of selecting departments for a particular division.

You could do this by having a controlling field that is a high level key in the lookup table. PeopleSoft’s use of Set IDs utilises this method to restrict values based on their Set ID.

On occasion though, you may need to be able to dynamically change your values on the fly. For example you may have a radio button on a page that you use to control the values displayed in a drop down list. One way of doing this is to use %RECNAME_EDIT functionality.

In this technique you specify a field as the prompt then set its value in PeopleCode to the name of the table you want to use as your prompt table.

First off set the Prompt Table for the field you want to display values for to %fieldname where fieldname is the name of the field you are going to set in your PeopleCode. For example PeopleSoft often use %RECNAME_EDIT. RECNAME_EDIT is a field in the DERIVED table.

Second, add your edit field to the page or pages that contain your dropdown field making it display only and invisible. For example if you used a prompt of %RECNAME_EDIT you could add RECNAME from the DERIVED record to the page.

Finally add some PeopleCode to set the value of this field to the actual name of the prompt table.

e.g.

If (condition) Then
DERIVED.RECNAME_EDIT = "A_VIEW_VW";
Else
DERIVED.RECNAME_EDIT = "B_VIEW_VW";
End-If;

This can be done in Page PeopleCode, Component PeopleCode or FieldChange PeopleCode on a controlling field.

Thanks to Kevin Gavaghan for giving me a heads up on this technique a while back.

Add to Google

One neat thing you can do with iGoogle is add a feed to a blog to your page. As a blogger, in order to facilitate this you need to add a button to your blog that the reader can press to add it.

To create this button on your blog: -

• Go to the following page http://www.google.co.uk/webmasters/add.html and enter your feed URL(e.g. http://feeds.feedburner.com/TheGeorgeLiteZone?format=xml) in the box.

• Press the Create HTML button.

• Copy the HTML generated.

• In Blogger add a new HTML/Javascript page element, or open an existing one.

• Paste in the HTML you copied.

Est voilla, a nifty little Add To Google button will appear as though by magic on your blog. Now anyone visiting it can add your blog to their iGoogle page. Fantastic.

Now to follow my own instructions to add a button to Coded Languages.

Query Tree Security SQL

Query Trees are assigned to a permission list in PeopleSoft under the Query tab of: -

PeopleTools > Security > Permissions and Roles > Permission Lists

Working out who's got access to what query trees can be a bit of a nightmare manually searching through each permission list in turn. To that end, the following piece of SQL is quite useful in establishing which roles have access to a particular query tree or trees.

select distinct ROL.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME in (Query Tree List)

To see which query trees a particular user has access to, use: -

select distinct USR.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLEUSER_VW USR,
PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where USR.OPRID = 'Operator ID'
and USR.ROLENAME = ROL.ROLENAME
and ROL.CLASSID = SAG.CLASSID

To see which users have access to a particular query tree: -

select distinct ROL.ROLENAME,
OPR.OPRDEFNDESC
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG,
PSROLEUSER_VW USR,
PSOPRDEFN OPR
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME = 'QRY_TMUK_ALL'
and ROL.ROLENAME = USR.ROLENAME
and USR.OPRID = OPR.OPRID
order by ROLENAME, OPR.OPRDEFNDESC