PeopleSoft

PeopleSoft related articles.

List of Active Accounts with no activity for a Fiscal Year

SQL to find active accounts that have no activity in a specific fiscal year. Notice the accounting_period <> 0 in the where clause. This is needed as accounts that had activities in 2007, for example, will have a row on the ledger table under year 2008 and accounting period of 0.

SELECT   a.ACCOUNT, a.descr
    FROM ps_gl_account_tbl a
   WHERE a.eff_status = 'A'
     AND NOT EXISTS (SELECT 'x'
                       FROM ps_ledger b
                      WHERE a.ACCOUNT = b.ACCOUNT
                      AND b.fiscal_year = :year

Hide a Subpage Using PeopleCode

There was a question asked on the forum on how to hid a subpage. There is no Peoplecode function to hide a subpage directly, BUT one way around that is putting the subpage in a group box and hid the group box instead. Hiding the group box will end up hiding your sub page.

Make sure to follow the following post, it will walk you through how to hide a group box on a page.

Delete PeopleSoft Query From the Database

There could be different reasons why a PeopleSoft developer would like to delete a query from the database. Upgrade clean up would probably be the most common one. Here is a function you can use to get you started.

Function DeleteQuery(&sQueryName As string)
   SQLExec("DELETE FROM PSQRYDEFN WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYSELECT WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYRECORD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELD WHERE QRYNAME=:1", &sQueryName);

Check Box Select/Deselect All on Grid

The below function is to be used on a grid with multiple check boxes. Place the code behind a FieldChange event and users will have the option to Select or Deselect grid rows all at once.

Function selectAllRows(&rs As Rowset)
   Local number &i;
   Local Row &row;
   
   For &i = 1 To &rs.ActiveRowCount
      &row = &rs.GetRow(&i);
      /* Make sure we only select visible rows. */
      If &row.Visible = True Then
         &row.Selected = True;
      End-If;
   End-For;
end-function;

/*main line*/
Local Rowset &rs;

Common Terminology Used in Web Services

Before start using PeopleSoft Integration Broker for sending and receiving services, you need to be familiar with the terminology used for Web services. In this post, I will try to list and describe common terms used for Web services.

Extensible Markup Language (XML): XML is a text-based format that provides a mechanism to describe document structures using markup tags. It allows developers to create their own customized tags, enabling the definition, transmission, validation, and interpretation of data between applications and between organizations.

Unlock PeopleSoft Objects All at Once (Change Control Locking)

Change control is used in Application Designer to lock definitions and track history changes for each object. Change control can be activated through Application Designer using Tools > Change Control > Administrator.

After few good size projects, you can easily have hundreds if not thousands of locked objects. Attempting to unlock the objects one by one is very time consuming, so here is a way to speed up the process.

Locked objects are stored on PSCHGCTLLOCK table. Here is how PeopleSoft describes the table:

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.