Ask Question
Help Others
Home › PeopleSoft

Queries

PeopleSoft Queries related articles.

Get Edit Tables Behind a Record Fields

Sun, 12/14/2008 - 4:05pm — Lepa

A straight forward SQL to get edit tables behind field(s) for a specific record. The SQL execludes any edit tables that start with "%" as those are dynamic edit tables with values populated by PeopleCode at run time and thus will not be of a good use in this query.

SELECT R.FIELDNAME 
 , R.EDITTABLE 
  FROM PSRECFIELDDB R 
  , PSDBFIELD F 
 WHERE R.RECNAME = :RecordName
   AND SUBSTR(R.EDITTABLE,1,1) <> '%'  
   AND R.EDITTABLE <> ' '  
   AND R.FIELDNAME = F.FIELDNAME
   AND F.FLDNOTUSED = 0;

A List of Pages a Peoplesoft Role Name can Access

Tue, 12/09/2008 - 5:19pm — Lepa

A query that will take a PeopleSoft role name as an input and returns all pages that could be access by that role. The query will also indicate what kind of operations a user can perform on that page. Example, Add Update/Display and so forth.

SELECT   b.menuname, b.barname, b.baritemname, d.pnlname, c.pageaccessdescr
    FROM psroleclass a,
         psauthitem b,
         pspgeaccessdesc c,
         pspnlgroup d,
         psmenuitem e
   WHERE a.classid = b.classid
     AND d.pnlgrpname = e.pnlgrpname
     AND b.menuname = e.menuname

What Permission Lists does this Role Have?

Tue, 12/09/2008 - 4:51pm — Lepa

A query that takes a PeopleSoft role name as a value and returns Permission List(s) associated to that role.

SELECT a.rolename, a.classid AS permission_list
  FROM psroleclass a
 WHERE a.rolename = :rolename;

Identify Records with a Specific Subrecord

Wed, 09/17/2008 - 9:08am — Lepa

A subrecord in PeopleSoft is a way to group fields together and place them on a record, view, or a temp table. Here is a query you can use to identify records that have a specific subrecord.

SELECT a.recname, a.fieldname, b.rectype, b.objectownerid
  FROM psrecfield a, psrecdefn b
 WHERE a.recname = b.recname
   AND a.fieldname = 'SUB-RECORD-NAME';

List of Active Accounts with no activity for a Fiscal Year

Mon, 07/07/2008 - 9:43am — Lepa

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

Traversing the Dept Security tree

Fri, 06/13/2008 - 10:45am — abhi10aug

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

Identify Records Behind a Page and Subpage

Mon, 06/09/2008 - 11:15am — Lepa

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'

Who Modified this PeopleCode?

Fri, 05/30/2008 - 2:12pm — Lepa

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

Thu, 05/29/2008 - 11:29am — Lepa

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 ?

Find Duplicate Objects in Application Desinger Projects

Tue, 05/13/2008 - 4:48pm — Lepa

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.