Finding Permission lists and Security details for PeopleSoft components by using SQL Queries

How do we retrieve all the menus, components and pages used for a particular PeopleSoft module? For example, if one had to get the list of all such components related to Payroll or ePerformance or any other module retrieving the list of pages, components and menus is pain some either doing via online or by any other non-SQL means. Using the translate values of OBJECTOWNERID field, we can get the list of related objects for a particular module and then query the PeopleTools meta tables to retrieve the rest of the information. How do we do this?

First find the list of translate values from OBJECTOWNERID field related to the module one wants to retrieve the list for. Then using the retrieved values substitute them in the below SQL query to retrieve the Menu name, Menu Bar Name, Bar Item Name and Page(Panel) Item Name.

SELECT C.MENUNAME, C.BARNAME, C.ITEMNAME, D.ITEMNAME
FROM
PSPNLGRPDEFN A,
PSPNLGROUP B,
PSMENUITEM C,
PSPNLGROUP D
WHERE A.PNLGRPNAME = B.PNLGRPNAME
AND A.MARKET = B.MARKET
AND A.OBJECTOWNERID IN (--Provide List of values --)
AND A.PNLGRPNAME = C.PNLGRPNAME
AND A.MARKET = C.MARKET
AND B.ITEMNAME = C.ITEMNAME
AND A.PNLGRPNAME = D.PNLGRPNAME
AND A.MARKET = D.MARKET
AND B.PNLNAME = D.PNLNAME
ORDER BY 1, 2, 3, 4;

But what do we do with the menu name, bar name and item names? We would like to have the security related values displayed for the components. So, we have to query PSAUTHITEM table. Only to find out the required values to be substituted in PSAUTHITEM table we wrote the above query. By passing the above retrieved values to PSAUTHITEM table, one can retrieve the required permission lists and relevant values. Here is a single SQL query which does this:

SELECT DISTINCT E.CLASSID, E.MENUNAME, E.BARNAME, E.BARITEMNAME, E.PNLITEMNAME, E.DISPLAYONLY, E.AUTHORIZEDACTIONS
FROM
PSPNLGRPDEFN A,
PSPNLGROUP B,
PSMENUITEM C,
PSPNLGROUP D,
PSAUTHITEM E
WHERE A.PNLGRPNAME = B.PNLGRPNAME --Component Name
AND A.MARKET = B.MARKET
AND A.OBJECTOWNERID IN (--Provide List of values --) /*List of values and this condition can be removed if run against a particular page or component or menu */
AND A.PNLGRPNAME = C.PNLGRPNAME
AND A.MARKET = C.MARKET
AND B.ITEMNAME = C.ITEMNAME -- Bar Item Name
AND A.PNLGRPNAME = D.PNLGRPNAME
AND A.MARKET = D.MARKET
AND B.PNLNAME = D.PNLNAME -- Page Name
AND E.MENUNAME = C.MENUNAME
AND E.BARNAME = C.BARNAME  --Menu Bar Name
AND E.BARITEMNAME = C.ITEMNAME -- Bar Item Name
AND E.PNLITEMNAME = D.ITEMNAME -- Page(Panel) Item Name
ORDER BY 1, 2, 3, 4, 5, 6, 7;

If the details are required for a particular page or component name then add conditions for PNLNAME with the required page name, PNLGRPNAME with the required Component Name and supply the required Menu related values accordingly.

For those who are not familiar with the bar and item names, a quick reference is provided below:

• MENUBARNAME - Menu bar name
• BARITEMNAME - Itemname used in the menu definition for the panel group(component).
• PNLITEMNAME - The Itemname used for the panel(page) in the component definition. 

To further take the list of roles and role users add the appropriate Security tables with the Permission lists retrieved from PSAUTHITEM.

The above SQL retrieves all the permission lists along with each of its content reference details, visibility and the actions allowed. If you would like to add permissions to a component, here is one of my previous posts -  Providing Permission to PeopleSoft components by updating PeopleTools tables