My Oracle Support Banner

SQL Queries for Troubleshooting Issues on Security, Component Structure, Pages, etc. (Doc ID 968656.1)

Last updated on MARCH 08, 2018

Applies to:

PeopleSoft Enterprise FIN Receivables - Version 8.9 and later
PeopleSoft Enterprise SCM Inventory - Version 8.9 and later
PeopleSoft Enterprise EPM Planning & Budgeting - Version 8.9 and later
PeopleSoft Enterprise CRM Online Marketing - Version 8.9 and later
PeopleSoft Enterprise HCM Human Resources - Version 8.9 and later
Information in this document applies to any platform.

Purpose

This document gives SQL queries with PeopleTools tables that are useful while troubleshooting issues with security, component structure and pages, etc. This document also includes a list of commonly used PeopleTools  tables in the PeopleSoft system. It can be used for any module  in PeopleSoft system,  as most  of the PeopleTools tables and  SQL scripts given in this document are generic.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Troubleshooting Steps
 To find the user profile if employee ID is  known, use the query:
 To find  all the permission lists used by a menu, or all the roles and user profiles that can access the menu,  if the menu name is known, use the query:
 To find all  users who have access to a particular page, and from what permission lists, use the query:
 To find the roles and permission lists associated with a particular user, use the query:
 To find the permission lists having access to a given component, use the query:
 To find all the roles with access to a given component, use the query:
 To find all the permission lists having access to a given web library, use the query:
 To find navigation to a page, do the following:
 To find the records containing a specific field if the field name is known, use the query:
 To find all the records and fields used by a page, use the query:
 To find the pages that reference a record and field if  RECNAME and FIELDNAME are known, use the query:
 To find all the fields used in a component, use the query:
 To find all the records  used in a component, use the query:
 To find the indexes that have been built on a table, use the query:
 To find the permission lists which grant access to Component Interface selected:
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.