PeopleSoft

PeopleSoft related articles.

PeopleSoft Object Types Explained

If you ever queried PSPROJECTITEM table (stores PeopleTools project objects) then you have definitely noticed that object types are stored as a number and not with their names. For example, if you have a project with a component then your component on this table will be stored as object type number 7.

So, what are other object types stored as (Pages, Record PeopleCode, Menus....)? Well, the list below is your answer. Please don't hesitate to post a comment and "correct" me if I messed up somewhere while mapping numbers to names!

0 = 'Record'
1 = 'Index'
2 = 'Field'

Extract Project Objects Using an SQL

There have been times where I needed to document objects included in an Application Designer. The process could get very time consuming if you have a project with hundreds of PeopleSoft objects in it. PeopleSoft doesn't provide a "copy paste" type of approach to ease things, so I came up with my own!

The query below will return all the objects inside of an Application Designer project. You can then copy the rows and paste them where ever you'd like.

SELECT (CASE OBJECTTYPE WHEN 0 THEN 'Record'
WHEN 1 THEN 'Index'
WHEN 2 THEN 'Field'
WHEN 3 THEN 'Field Format'

Query for Component Path within PeopleSoft Portal

While back, I've created an SQL to find the exact same thing but with some level limitations. I have came across another query written by Jim's PeopleSopft Journal blog that works a little bit better and thought to share it with my visitors.

Jim's query however requires the developer to know the content reference of a component before the query could be used. What I really wanted is the ability to find the path by just knowing the component and not the content reference. Chances are, if you know the component reference you know the path to that component as well. So, to find the component path without knowing the content reference, follow the steps below:

1) Run the below SQL to get the content reference name for your component

SELECT PORTAL_NAME,
 PORTAL_OBJNAME AS CONTENT_REFERENCE,
 PORTAL_LABEL,
 PORTAL_URI_SEG1 AS MENU,
 PORTAL_URI_SEG2 AS COMPONENT,
 PORTAL_URI_SEG3 AS MARKET
FROM psprsmdefn
 WHERE PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_URI_SEG2 = :1;

-- Replace :1 with the component name you are looking for.

Find Component Path Within PeopleSoft Portal

Update: You might want to check my new post to find the path with no level limitations.

PeopleSoft Portal Link as an External URL

I had fun trying to figure this one out at work today. I wanted to
associate a link in PeopleSoft Portal to open an external URL (website)
in a new window and without the portal frame (template) around it. To
do so, follow the steps below:

Navigate to PeopleTools >
Portal > Structure and Content. Chose the folder you would like your
link to be in or you can add a new folder just for this new link if
you'd like.

Now scroll all the way to the bottom and click "Add Content Reference".

Run an SQR From a PeopleSoft Application Engine

Here is a good sample of how to launch an SQR from within PeopleCode. You can use the peoplecode function called “ScheduleProcess”. Here is the example:

&PROCESS_NAME = "SQRNAME";
&PROCESS_TYPE = "SQR Report"; (or "SQR Process")
&RUN_CNTL_ID = "YOUR_RUN_ID";
&RUN_CONTROL = ScheduleProcess(&PROCESS_TYPE, &PROCESS_NAME, "2", &RUN_CNTL_ID, &PRCS_INST);

If &RC != 0 /*"1" = client "2" = server*/
Winmessage("Error: Error Scheduling SQR Process, RC = " | &RUN_CONTROL);
End-If;

Calling an Application Engine from PeopleCode

The PeopleCode below will show you how to call an Application Engine from PeopleCode. Some refer to this as “Real-Time Application Engine Processing.” For example, when a user save’s a page, you may want a specific App engine program to run. You can also execute this code in a field change event.

The following code calls the Application Engine program named YouAppEngProg, and passes it the necessary initialization values.

&MYRECORD = CreateRecord(RECORD.MY_INIT_VALUES);

PeopleSoft Validate AP Business Unit

The select statement below is preformed by AP Voucher Build (AP_VCHRBLD) Application Engine to validate an AP Business Unit:

INSERT INTO %TABLE(VCHR_HDR_QV_ER) (PROCESS_INSTANCE
, BUSINESS_UNIT
, VOUCHER_ID
, MESSAGE_SET_NBR
, MESSAGE_NBR
, FIELDNAME
, MESSAGE_PARM1
, MESSAGE_PARM2
, MESSAGE_PARM3)
SELECT %Bind(PROCESS_INSTANCE)
, HDR.BUSINESS_UNIT
, HDR.VOUCHER_ID
, 7220
, 1
, 'BUSINESS_UNIT'
, HDR.BUSINESS_UNIT
, ' '
, ' '
FROM %TABLE(VCHR_HDR_TMP) HDR
WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND BUSINESS_UNIT NOT IN (
SELECT BUSINESS_UNIT

PeopleSoft Validate Vendor ID

The select statement below is preformed by AP Voucher Build (AP_VCHRBLD) Application Engine to validate a Vendor ID:

INSERT INTO %TABLE(VCHR_HDR_QV_ER) (PROCESS_INSTANCE
, BUSINESS_UNIT
, VOUCHER_ID
, MESSAGE_SET_NBR
, MESSAGE_NBR
, FIELDNAME
, MESSAGE_PARM1
, MESSAGE_PARM2
, MESSAGE_PARM3)
SELECT %Bind(PROCESS_INSTANCE)
, HDR.BUSINESS_UNIT
, HDR.VOUCHER_ID
, 7220
, 2
, 'VENDOR_ID'
, HDR.VENDOR_SETID
, HDR.VENDOR_ID
, ' '
FROM %TABLE(VCHR_HDR_TMP) HDR
WHERE HDR.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND VENDOR_ID NOT IN (
SELECT VENDOR_ID

Application Engine ReUse Statement in PeopleSoft

The ReUse in Application Engine was brought to my attention by a great blog that I follow regularly.  The original post can be found at PeopleSoftTipster

The ReUse statement setting on an Application Engine is a great way to increase performance. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.