SQL Query to identify the Level 0 record of a component

The below SQL will give all the records of a component along with its corresponding occurs levels:

SELECT DISTINCT D.recname     TableName,
                D.occurslevel ScrollName
FROM   pspnlgrpdefn A,
       pspnlgroup B,
       pspnldefn C,
       pspnlfield D,
       psrecdefn E
WHERE  A.pnlgrpname = B.pnlgrpname
       AND A.market = B.market
       AND B.pnlname = C.pnlname
       AND C.pnlname = D.pnlname
       AND A.pnlgrpname = 'POSITION_DATA' --Component Name
       AND E.recname = D.recname
       /* Restricts to only tables and eliminates views and derived records */
       AND E.rectype IN ( 0 )
       /*This condition eliminates records in component as Related Fields*/
       AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER  BY 2,1 

Note: Adding condition as d.occurslevel = 0 will return only the level 0 record

SQL Query to find " What query TREE(s) and ACESS GROUP(s) does this record currently belong? - PS Query Security

What query TREE(s) and ACCESS GROUP(s) does this record currently belong?



SELECT A.TREE_NAME, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFF_STATUS, A.DESCR, TO_CHAR(A.LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), A.LASTUPDOPRID, B.PARENT_NODE_NAME, B.TREE_NODE, B.TREE_NODE_TYPE, B.TREE_NODE_NUM, B.PARENT_NODE_NUM,A.SETID,A.SETCNTRLVALUE
FROM PSTREEDEFN A, PSTREENODE B
WHERE A.SETID = ' '
AND A.TREE_STRCT_ID = 'ACCESS_GROUP'
AND A.SETID = B.SETID
AND A.SETCNTRLVALUE = B.SETCNTRLVALUE
AND A.TREE_NAME = B.TREE_NAME
AND B.TREE_NODE = :1
ORDER BY 1, 11

PeopleSoft Query Security

If a PeopleSoft record built through Application designer is not listed in PS-Query tool for reporting, it means that the particular PeopleSoft record is not available for the user to access through PS-Query.

For example, one has built a custom record PS_Z_ABC through Application Designer, then the record - Z_ABC won't be available under for PS-Query for any user. The record has to be added to a query tree.

Why to add a record to a Query Tree? How to add a record to Query Tree?
Well, PeopleSoft Queries are used to build SQL queries and retrieve information from application tables. For each PeopleSoft Query user, you can specify the records the user is allowed to access when building and running queries. This is done by creating query access groups in PeopleSoft Tree Manager and then assigning users to these query access groups with PeopleSoft Query security. PeopleSoft Query security is enforced only when using PeopleSoft Query; it does not control runtime page access to table data.

Once a decision is made on what kind of users (based on Permission lists) must have access to the record, then the record has to be attached to the query tree associated with the permission list. This can be done under:
PeopleTools > Security > Query Security > Query Access Manager
Most often, it is better to run Query Access Cache process immediately after making changes to any query tree.


How to associate a Query Tree with a user/OPRID?
This is done based upon the Permission lists attached to the users' roles. To associate a Permission List with a particular Query Tree, at first place the query tree with required access groups must be created. Once a query tree with required access groups is available then associate this query tree with a permission list under: PeopleTools > Security > Permissions and Roles > Permission Lists > Query 

In this navigation, click on "Access Group Permission"; then chose the Tree name and the access group in the tree for which you want to give access to. 



How to identify which user/permission list has access to which tree?

1. We can verify this by clicking "Access Group Permission" in the navigation: PeopleTools > Security > Permissions and Roles > Permission Lists > Query
2. Alternatively, we can check this through database as well. PeopleSoft has a delivered record named - SCRTY_ACC_GRP which maintains the permission lists and its associated query trees along with the access group and access properties. The structure of this record PS_SCRTY_ACC_GRP is as below:

  1. CLASSID               Key field
  2. TREE_NAME         Key field
  3. ACCESS_GROUP  Key field
  4. ACCESSIBLE        Non-Key field with default value as 'Y'