PeopleSoft

PeopleSoft related articles.

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

Nightly batch processes????

Guys.... In this post I'm just sharing my thoughts on nightly batch processes rather than writing any technical solution or design.

The other day we had some issues with a few of the batch processes running in our production. Some of the custom mass update processes where running for hours which slowed down the system and in turn crashing one of the app servers. Luckily we have failover app servers configured, so we didn't receive hundreds of emails and tickets logged by the end users. :-)

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.

PeopleSoft Financials Tables

A wiki for the community to start organizing PeopleSoft Financials tables in one place. This is different than PeopleSoft meta-tables and they should not be included in this wiki.

General Ledger Tables

PS_LEDGER: The general ledger data table.

PS_JRNL_HEADER: Stores general ledger journal header data.

PS_JRNL_LN: Stores general ledger journal line data.

ps_combo_rule_tbl: Stores ChartField combination rules.