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

Last updated on SEPTEMBER 08, 2023

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
Generic (Platform Independent)

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

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:

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