Lepa's blog

Show Users Assigned to a Specific Role

In a previous post I showed you how to know what Roles are assigned to a specific user. But here is how you find out what Users are assigned to a specific Role.

SELECT C.OPRID ,
  C.OPRDEFNDESC ,
  A.ROLENAME    ,
  A.DESCR
   FROM PSROLEDEFN A,
  PSROLEUSER B      ,
  PSOPRDEFN C
  WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID        = B.ROLEUSER
AND B.ROLENAME LIKE UPPER(:1)
GROUP BY C.OPRID,
  C.OPRDEFNDESC ,
  A.ROLENAME    ,
  A.DESCR
ORDER BY C.OPRID;

Show Roles Assigned to a Specific User

Here is a query that I often use to lookup Roles assigned to a specific PeopleSoft user.
At run time, replace :1 with OPRID your are looking for OR user name (partial search also works).

 SELECT C.OPRID,
  C.OPRDEFNDESC         ,
  A.ROLENAME            ,
  A.DESCR
   FROM PSROLEDEFN A,
  PSROLEUSER B      ,
  PSOPRDEFN C
  WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID        = B.ROLEUSER
AND (C.OPRID      =:1
OR C.OPRDEFNDESC LIKE upper(:2))
GROUP BY C.OPRID,
  C.OPRDEFNDESC ,
  A.ROLENAME    ,
  A.DESCR;

Roles, Permissions, and Users Assigned to Portal Content Reference or Component

Roles attached to a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT c.portal_name, c.portal_objname, b.rolename, e.descr
  FROM psroleclass b, psprsmperm c, psprsmdefn d, psroledefn e
    WHERE c.portal_objname = d.portal_objname
            AND b.classid = c.portal_permname
            AND b.rolename = e.rolename
            AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);

Permissions attached to a component (portal_uri_seg2) or content reference (portal_objname)
 

Try and Catch Built-in Function

This is a simple example on when and how to use the Try and Catch built-in function. The code below will fetch a field value from a scroll area on a page and delete the row depending on the value. If the value is >= 0 or if it is a "charachter" value, then go ahead and mark the row for deletion.

See code comments to get a better idea of what the code is doing.

Local number &someValue;
Local string &strSomeValue;
Local Rowset &RS;

/*fetch result set;*/
&RS = GetLevel0()(1).GetRowset(Scroll.scroll_table);
&RS.Flush();

Oracle SQL Developer 1.5

Oracle SQL Developer 1.5 Early Adopter is out. I have been using 1.2 for a while and started testing 1.5 couple days ago. As this is still not the official release, I would have to say things are going pretty good and no bugs so far.

Order Portal References and Folders Using Sequance Number

To make things easier on users, you are most likely to order portal content references and portal folders in an alphabetical order. To do that, you need to navigate to your portal structure page (Portal Administrator role required), and edit each content reference and folder sequence numbers to be equal, for example, set them all to 100.

When all of your content references are set to be the same number PeopleSoft will automatically sort them alphabetically.

Set Number of Links within the Portal Navigation

The picture below shows a Security folder and 2 sub-folders (User Profiles and Permissions & Roles). You can also notice that each sub-folder has 4 more links listed below it (in addition to the More... link). If you are wondering why 4 links and not 6 or 10 links, well, it is 4 links because this is what I want it to be.

How can you set the number of links available under a Portal folder?
Navigate to PeopleTools > Portal > Portal Utilities > System Options and update "Maximum Child Links" to 4.

ORA-01775 - Looping chain of synonyms

I Never had this error before while trying to SELECT from a table, but there is always fist time for everything I guess. So, after few minutes of troubleshooting I found out that there is a synonym for the table on DBA_SYNONYMS table but the actual table object doesn't exist on DBA_OBJECTS table.

How did this happen? Well, my guess is someone dropped the table but never did the same for the synonym. So, the synonym ends up referencing itself when ever you select from that table and thus the “Looping chain of synonyms".

HideAllRows Hides Grid on a Page

I have been used to turn to HideScroll and UnHideScroll functions to hide/unhide grid on a page. These two functions however, remain for backward compatibility only.

The functions have been replaced by HideAllRows and ShowAllRows methods. The methods are equivalent to a loop setting the visible propertly of each row of the rowset to false.
For the code to work though, you need to place your peoplecode in a parent rowset and execute it against a child rowset.

Read a File Using File Layout

A sample code to read a file using file layouts and insert data read into a record.

Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &FILE_DIRECTORY, &FileName;

&FileName = "MY_FILE_NAME.txt";
&FILE_DIRTORY = "/MYDIRECTORY/"
/*open file for reading*/
&MYFILE = GetFile(&FILE_DIRECTORY | &FileName, "R", %FilePath_Absolute);
/*create record object*/
&REC = CreateRecord(Record.MY_RECORD);
&ARRAY = CreateArrayRept("", 0);
/*check if file is open*/
If &MYFILE.IsOpen Then