Find Distribution List for a PeopleSoft process [SQL]
Who can access a report from PeopleSoft report manager ?
or
Who is getting the reports emailed out to them ?
This is what is defined by the distribution list for each scheduled process. Let’s see how to find this information quickly in PeopleSoft.
What is a PeopleSoft distribution list ?
PeopleSoft distribution list, refers to the group of users who are authorized to access a report/process output in Report manager. If the output type is email (and users’ email is stored in the system), then these users will get the email with the report output.
How to setup distribution list for a process in PeopleSoft?
You can setup distribution list for a process/report that is run on process scheduler by clicking the ‘Distribution’ link when submitting the process.
Distribution list can be set up by either a ‘Role’ or a ‘User ID’. So everyone who falls under this distribution list will be able to access the report.
In this example, we’re trying to run AEMINITEST application engine program and have setup distribution list based on role ‘PeopleSoft Administrator’ and user ID ‘VP1’.
Once this process completes, the output will be available for the user ‘VP1’ and anyone who has a ‘PeopleSoft Administrator’ role.
As you can see, it’s simple to setup but when you’ve a lot of processes/reports that are distributed to different group of users, then you need an easy way to find out this information.
How to Find Distribution List for a PeopleSoft process/report?
Details for distribution list are stored in PS_CDM_AUTH table. This tables stores the PRCSINSTANCE, DISTID & DISTIDTYPE.
DISTIDTYPE has value of either ‘2’ or ‘3’. Value of 2 is for User ID and 3 is for role.
Now we just need to join this table with process request table PSPRCSRQST (to get the process instance details) & with PSOPRDEFN (to get the user ID details).
Below SQL can be used to find the distribution list for a PeopleSoft process/report that is scheduled to run on PeopleSoft process scheduler. It will list distribution by the role and user id both.
Interested in more SQLs to make your job easy, check out the below.
Find PS Query set on recurrence (SQL)
Find navigation to a PeopleSoft component (SQL)
