PeopleSoft

PeopleSoft related articles.

Send Emails from PeopleCode (SendMail Function)

You can use the SendMail PeopleCode function to send emails from within PeopleCode. You can also call this function from an Application Engine.

Note: Make sure your SMTP server is configured properly or the SendMail function will fail.

Local string &MAIL_CC, &MAIL_TO, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TITLES, &MAIL_TEXT, &MAIL_FILES, &MAIL_FROM, &REPLYTO, &SENDER;
Local number &MAIL_FLAGS;

&MAIL_FLAGS = 0;
&MAIL_TO = "email-address-message-going-to";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Test email";

Compare data in different tables with same structure

Here is the SQL to compare the differences in the data in different tables with same structure. Will be helpful for comparing the data between databases. This works in MS SQL Server if both databases are on same server or if there is a linked server configured for the other database.

SELECT  * FROM
(SELECT Max(TableName) AS TableName, FIELD1, FIELD2, FIELD3
FROM (SELECT 'HRDB1..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB1..PS_MY_RECORD
UNION ALL SELECT 'HRDB2..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB2..PS_MY_RECORD ) A  

Pass Dynamic Value to a Grid Label

A grid label is the blue (normally) colored grid header that you see on PeopleSoft pages. The grid text label can be easily edited using the Grid Properties in app designer. Once you have the grid properties box open, click the Label tab > Properties and there you can see the Label Text area. You can choose between a Static Text type or Message Catalog. I would almost always go with Message Catalog type as this could be easily updated online.

PeopleSoft Application Development

Lets start with the basics of application development in PeopleSoft. Well, I hope you all know that Application Designer is the tool which we used to develop PeopleSoft application and we usually call it as App Designer in short. Basically there are 8 steps in PeopleSoft Application Development.

Here are the 8 steps of PeopleSoft Application Development:

1. Design the application
2. Define new fields
3. Create record definitions
4. Build the records
5. Create page definition
6. Define the component
7. Registering the component
8. Test the application

Security Tables

PSOPRDEFN - UserIds created.
PSCLASSDEFN - Permission List definition.
PSROLEDEFN - Role Definition table
PSAUTHITEM - Menus, Components and Pages attached to a permission list.
PSROLEUSER - Contains user and role mapping.
PSROLECLASS - Role Permission List mapping.

PSAUTHBUSCOMP -- Component Interfaces Permissions
PSAUTHCHNLMON - Message Channels tagged to any particular PLs
PSAUTHMP - Mobile Pages tagged to a particular Permission List

PSUSERATTR
PSUSEREMAIL
PSOPRALIAS
PSOPRALIASTYPE
PSOPRALIASFIELD
PSROLECANGRANT
PSAUTHCUBE
PSAUTHOPTN
PSAUTHPRCS
PSAUTHSIGNON

PeopleSoft IDs

User Id

The User Id is the ID associated with the individual user logging onto the PeopleSoft application, via PeopleTools.Validation of this ID is performed by the PeopleTools themselves at log-in time, by virtue of verifying that a row has been defined in the PSOPRDEFN table for this ID. The row in the PSOPERDEFN table contains information pertinant to PeopleSoft internal security administration, such as the access profile defining what areas of the application this ID will have access to, and a link to the ACCESS ID (defined later) associated with the UserId.

Rowcounts in PS tables

Here is the SQL that you can use to fetch the number of rows in the PS tables in MS SQL Server database.

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
-- Declare cursor
DECLARE TABLES CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME LIKE 'PS_%'
-- Create the table
CREATE TABLE #tables (
tablet CHAR (255),
RecordCount INT)
-- Open the cursor
OPEN TABLES
-- Loop through all the tables in the database
FETCH NEXT
FROM TABLES
INTO @tablename

WHILE @@FETCH_STATUS = 0

Send User to a Portal Folder

Sometimes you would want to give users the option to click a button on the page and send them back to the Portal Folder they came from, or may be couple folders back! Here is how you go about doing that.

You can actually use the PeopleSoft delivered function NavPageURL to accomplish this! So, lets get started!

On your page, go ahead and add a button. Place the following PeopleCode at the Component Record Field level under FieldChange event.

Declare Function NavPageURL PeopleCode EOPP_SCRTN_WRK.FUNCLIB FieldFormula;

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;