Lepa's blog

Upload Files in PeopleSoft (File Attachment)

Giving PeopleSoft users the ability to upload files from a page by using the infamous "Browse" button is something that you will most likely do at one point in your PeopleSoft career. As a matter of fact, some questions are popping up on the forum asking for a sample code to get the above requirement accomplished.

Identify Records Behind a Page and Subpage

Use the following query to get records behind not only the page but sub-pages on a page.

WITH my_data AS
     (SELECT   subpnlname
          FROM pspnlfield
         WHERE pnlname = :1 AND subpnlname <> ' '
      GROUP BY subpnlname)
SELECT   recname
    FROM pspnlfield, my_data
   WHERE pnlname = :1 OR pnlname = my_data.subpnlname
GROUP BY recname;

Same query as above but written differently (suggested by a colleague of mine):
SELECT   recname
    FROM pspnlfield a
   WHERE pnlname = :1
      OR EXISTS (
            SELECT 'x'

Convert From One Currency to Another

Always return to Peoplebooks when you are about to write a new function. A quick search might save you a lot of time. This is what I've done when I was about to write a new function to convert currency from US dollar to any other currency on the PS_RT_RATE_TBL (rate table).

PeopleSoft already delivers such a function for you to use. The function is ConvertCurrency.

ConvertCurrency(amt, currency_cd, exchng_to_currency, exchng_rt_type, effdt, converted_amt [, error_process [, round] [, rt_index]])

Who Modified this PeopleCode?

Nothing frustrates me more than developers modifying delivered PeopleSoft code without adding comments. It is easy to figure out who was the last person to touch a record by clicking on the Record Properties button in application designer. BUT it is not that easy when it comes to PeopleCode.

I have written the following SQL to help me identify the last person to modify a Record Field PeopleCode Event.

SELECT objectvalue1 record_name, objectvalue2 field_name,
       objectvalue3 peoplecode_event, lastupddttm, lastupdoprid

Pages with Secondary Pages That Use a Field

Here is a question that was asked on the forum and thought it will be of a value to add it to the PeopleSoft handbook to keep for future references. The question was:

Is there a way to determine all the pages with a secondary page with a specific field ?
For e.g. if I am looking for emplid field and determine that the emplid field is used on say 5 secondary pages. I now need to tie those secondary pages to the pages .
Is there a sql that can be run to identify such pages ?

PeopleSoft Object Types Definitions

PeopleSoft stores object definitions types such as Record, Field and SQL definitions as numbers in PeopleTools meta-tables. Here is a list of what each number means with respect to its definition.

Definitions for Record Object Types

The following are stored on the PSRECDEFN table on RECTYPE field.
RECTYPE = 0
Corresponds to: SQL Table
Comments: The record will be built as an actuall database table.

Check for Data Duplicates on a Grid

Here is a piece of code to prevent duplicate data on a specific field on a page grid. You can of course modify it to check for multiple fields or even the whole row.

   /* Check for data duplicates on a grid. */
   Local Row &row1, &row2;
   Local number &r, &r1;
   
   &rs = GetLevel0().GetRow(1).GetRowset(Scroll.grid_table);
   
   For &r = 1 To &rs.ActiveRowCount
   /*Get grid row*/
      &row1 = &rs.GetRow(&r);
      /*once we have a row, we are going to loop through the grid rows and make sure a specific field value is unique*/

PeopleSoft Web Services 101

I'm not an expert on Web Services. This is just an attempt to provide a basic, simple introduction for CompShack visitors. Please feel free to comment or suggest any additions to the topic.

What is a Web Service?
From how I understand it, a Web Service is a set of technologies based on a set of standards to build applications that can "talk" to each other. In other words, Web Service is an application that exists over the web.

Find Duplicate Objects in Application Desinger Projects

The sql will identify duplicate objects in different application designer projects to eliminate duplicate work by developers.

You will find the SQL very handy during the analysis phase of a peoplesoft upgrade project. You 1st need to identify your projects and run compare and report through application designer. This will flag objects in the source database that are different than the target database to be marked for upgrade.

Permission Lists Assigned to a User

SQL that I find useful in many occasions. It will return a list of permissions that are assigned to a specific user.

SELECT   d.oprid, d.oprdefndesc, c.roleuser, a.rolename, a.classid,
         b.classdefndesc
    FROM psroleclass a, psclassdefn b, psroleuser c, psoprdefn d
   WHERE a.classid = b.classid
     AND c.rolename = a.rolename
     AND d.oprid = c.roleuser
     AND d.oprid = :userid
GROUP BY d.oprid,
         d.oprdefndesc,
         c.roleuser,
         a.rolename,
         a.classid,
         b.classdefndesc;