PS Query & SQL

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi Anne,

I believe there is a Chrome plug-in that some people use. I'm sure if there's someone who uses that, they'll chime in. Personally, I use a query:

SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME 
  FROM PS_CS.PSPNLFIELD A 
  WHERE ( A.PNLNAME = :1 
     AND A.LBLTEXT <> ' ' 
     AND A.FIELDNAME <> ' ' 
     AND A.RECNAME <> 'PSXLATITEM' 
     AND A.RECNAME NOT LIKE '%WRK' 
     AND A.RECNAME NOT LIKE '%DERIVED%' 
     AND A.RECNAME NOT LIKE 'FUNCLIB%' ) 
  ORDER BY 2;

The prompt is for the page name. Just in case you weren't aware, when you're on the page in question Ctrl+Shift+J and you'll get an information page with the page name. Plug that into the prompt for that SQL and you'll get the list of fields & records along with the description from the page to help with matching.

The SQL excludes working & derived tables (since you can't really query those). So, it's not a 100% answer, but most of the time it gets me what I need to query.

I hope this helps.

Thanks,

Sarah

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi Anne,

I believe there is a Chrome plug-in that some people use. I'm sure if there's someone who uses that, they'll chime in. Personally, I use a query:

SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME 
  FROM PS_CS.PSPNLFIELD A 
  WHERE ( A.PNLNAME = :1 
     AND A.LBLTEXT <> ' ' 
     AND A.FIELDNAME <> ' ' 
     AND A.RECNAME <> 'PSXLATITEM' 
     AND A.RECNAME NOT LIKE '%WRK' 
     AND A.RECNAME NOT LIKE '%DERIVED%' 
     AND A.RECNAME NOT LIKE 'FUNCLIB%' ) 
  ORDER BY 2;

The prompt is for the page name. Just in case you weren't aware, when you're on the page in question Ctrl+Shift+J and you'll get an information page with the page name. Plug that into the prompt for that SQL and you'll get the list of fields & records along with the description from the page to help with matching.

The SQL excludes working & derived tables (since you can't really query those). So, it's not a 100% answer, but most of the time it gets me what I need to query.

I hope this helps.

Thanks,

Sarah

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi Anne,

I believe there is a Chrome plug-in that some people use. I'm sure if there's someone who uses that, they'll chime in. Personally, I use a query:

SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME 
  FROM PS_CS.PSPNLFIELD A 
  WHERE ( A.PNLNAME = :1 
     AND A.LBLTEXT <> ' ' 
     AND A.FIELDNAME <> ' ' 
     AND A.RECNAME <> 'PSXLATITEM' 
     AND A.RECNAME NOT LIKE '%WRK' 
     AND A.RECNAME NOT LIKE '%DERIVED%' 
     AND A.RECNAME NOT LIKE 'FUNCLIB%' ) 
  ORDER BY 2;

The prompt is for the page name. Just in case you weren't aware, when you're on the page in question Ctrl+Shift+J and you'll get an information page with the page name. Plug that into the prompt for that SQL and you'll get the list of fields & records along with the description from the page to help with matching.

The SQL excludes working & derived tables (since you can't really query those). So, it's not a 100% answer, but most of the time it gets me what I need to query.

I hope this helps.

Thanks,

Sarah

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi Anne,

I believe there is a Chrome plug-in that some people use. I'm sure if there's someone who uses that, they'll chime in. Personally, I use a query:

SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME 
  FROM PS_CS.PSPNLFIELD A 
  WHERE ( A.PNLNAME = :1 
     AND A.LBLTEXT <> ' ' 
     AND A.FIELDNAME <> ' ' 
     AND A.RECNAME <> 'PSXLATITEM' 
     AND A.RECNAME NOT LIKE '%WRK' 
     AND A.RECNAME NOT LIKE '%DERIVED%' 
     AND A.RECNAME NOT LIKE 'FUNCLIB%' ) 
  ORDER BY 2;

The prompt is for the page name. Just in case you weren't aware, when you're on the page in question Ctrl+Shift+J and you'll get an information page with the page name. Plug that into the prompt for that SQL and you'll get the list of fields & records along with the description from the page to help with matching.

The SQL excludes working & derived tables (since you can't really query those). So, it's not a 100% answer, but most of the time it gets me what I need to query.

I hope this helps.

Thanks,

Sarah

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

If using Chrome check out the following Google extensions.  

PS Utilities
PSChrome

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

If using Chrome check out the following Google extensions.  

PS Utilities
PSChrome

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi Anne,

I put together a simple document a long time ago.  It is mostly US Payroll focused, but if you're putting something together, you may like it as a guide:

https://drive.google.com/file/d/1e0M97EF5iYsGCDWerU20THAslxzfA8Xf/view?usp=sharing

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Thank you so much for sharing your document :)

Hi Anne,

I put together a simple document a long time ago.  It is mostly US Payroll focused, but if you're putting something together, you may like it as a guide:

https://drive.google.com/file/d/1e0M97EF5iYsGCDWerU20THAslxzfA8Xf/view?usp=sharing

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

Hi Anne!

I think the worth of the PS Utilities Chrome plugin (that also works in Edge) was not highlighted. It is an invaluable tool and provides exactly what you are looking for - a way to point to a field and know its origin. You can also get App Designer Page and Component information just as easily, and there are a few other nice options as well. Since you are new, I will mention that you will see field names that may come from views or are calculated on the fly called Derived fields that only hint at where they come from but sometimes have the word "Derived".  Here is a screen shot of a page that has a lot of derived fields - pointing to the Total you can see the field name above in the PS Utilities section. You can see that it also shows the Username and the environment instance. You get this look just by pressing the Looking glass in PS Utilities to toggle it on and off. Then simply float on any field.

Thank you so much for sharing your document :)

Hi Anne,

I put together a simple document a long time ago.  It is mostly US Payroll focused, but if you're putting something together, you may like it as a guide:

https://drive.google.com/file/d/1e0M97EF5iYsGCDWerU20THAslxzfA8Xf/view?usp=sharing

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University

You should use the Entity Relationship Diagrams.  They're typically loaded, only into a demo instance.

Check out these MOS articles:

Where to Find ERD (Entity Relationship Diagrams) for PeopleSoft Enterprise HCM 8.8 SP1, 8.9, 9, 9.1 and 9.2 Releases? (Doc ID 1051162.1)

PeopleSoft Human Capital Management 9.2 Entity Relationship Diagrams (Doc ID 1559851.1)

Hi Anne!

I think the worth of the PS Utilities Chrome plugin (that also works in Edge) was not highlighted. It is an invaluable tool and provides exactly what you are looking for - a way to point to a field and know its origin. You can also get App Designer Page and Component information just as easily, and there are a few other nice options as well. Since you are new, I will mention that you will see field names that may come from views or are calculated on the fly called Derived fields that only hint at where they come from but sometimes have the word "Derived".  Here is a screen shot of a page that has a lot of derived fields - pointing to the Total you can see the field name above in the PS Utilities section. You can see that it also shows the Username and the environment instance. You get this look just by pressing the Looking glass in PS Utilities to toggle it on and off. Then simply float on any field.

Thank you so much for sharing your document :)

Hi Anne,

I put together a simple document a long time ago.  It is mostly US Payroll focused, but if you're putting something together, you may like it as a guide:

https://drive.google.com/file/d/1e0M97EF5iYsGCDWerU20THAslxzfA8Xf/view?usp=sharing

Hi, I'm new to Peoplesoft. Previous systems I've worked with show the database and field name when you hover over the record so I can figure out where to get data in the database and it seems PeopleSoft doesn't have this functionality. I have to run reports that join job, HR, payroll with finance (fund, program, project) data. Does anyone have a list of pages mapped to DB tables, data dictionary or even just a list of database tables with more detailed descriptions?

Thank you!

Anne

Simon Fraser University