Blogs

Traversing the Dept Security tree

We can use the connect by prior function provided by Oracle to traverse the Dept security Tree . Suppose an Organization has 7 levels in Dept tree i.e. 1 to 7 .
We want to traverse the dept tree from level 3 to level 6.
Also we know the deptid at the level 3 say 'LVL3-DPT' .

We can use the following SQL to traverse the whole tree (3-6) starting from 'LVL3-DPT'. Only child of 'LVL3-DPT' will be seen in sql output. The starting deptid has to be used with "START WITH " clause.

SELECT TR.TREE_NODE , TR.TREE_LEVEL_NUM        

FROM PSTREENODE TR

Generic Scroll PeopleCode

We always find it confusing to write code for Scrolls. Following is generic peoplecode which you can use for pages having parent child relationships (scrolls).

Just replace the record and field for respective level.

&rs_level0 = GetLevel0();

&level0fieldvalue = &rs_level0.(1).Level0_Record.Level0_field.Value ;

&row_level0 = &rs_level0.GetRow(1);
&rs_level1 = &row_level0.GetRowset(Scroll.Level1_Record);
For &J = 1 To &rs_level1.RowCount

   &level1fieldvalue = &rs_level1.GetRow(&J).Level1_Record.Level1_field.Value
   
   &row_level1 = &rs_level1.GetRow(&J);

Writing XLS file using SQR and PeopleCode

We can write a XLS file using PeopleCode and SQR. We will be simply writing a XLS file using HTML tags.
Below is the SQR code.

Let $filename='C:\PeopleSoft\PsftHRMS90\'||$Prcs_Process_Instance||'\Report'||$Prcs_Process_Instance||'.xls'
Open $filename  as 2 for-writing record=20000 status=#filestat2
<*start of  table *>
Write 2 from '<table border = 1>'

<*start of heading row*>
Write 2 from '<tr>'
<*heading cell tag, we can have multiple headings cells in a heading row*>
Write 2 from '<th>'
Write 2 from  $head
Write 2 from '</th>'
<*end of heading cell tag*>

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*/